cancel
Showing results for
Did you mean:
Helper V

## Find value if date falls between START and END dates

Hi, I have below two tables and required output.

If date from Table1 fall between Table2 START and END Dates, then i need name from Table2.

 Table 1 Table2 ID Created Date Name Start Date End Date 1 6/30/2022 A 7/20/2022 8/2/2022 2 5/15/2022 B 8/3/2022 8/16/2022 3 7/25/2022 C 8/17/2022 8/30/2022 4 8/1/2022 D 8/31/2022 9/20/2022 5 7/10/2022 6 8/10/2022 Output ID Created Date Name 1 8/30/2022 C 2 9/10/2022 D 3 7/25/2022 A 4 8/1/2022 A 5 8/28/2022 C 6 8/10/2022 B

Thanks

2 ACCEPTED SOLUTIONS
Super User

@manalla Try:

Column =
VAR __Date = 'Table 1'[Created Date]
VAR __Name = MAXX(FILTER('Table2',__Date >= [Start Date] && __Date <= [End Date]),[Name])
RETURN
__Name

Measure =
VAR __Date = MAX('Table 1'[Created Date])
VAR __Name = MAXX(FILTER('Table2',__Date >= [Start Date] && __Date <= [End Date]),[Name])
RETURN
__Name

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

Hi,

Try this calculated column formula in Table1

=CALCULATE(MAX(Table2[Name]),FILTER(Table2,Table2[Start Date]<=EARLIER(Table1[Created Date])&&Table2[End Date]>=EARLIER(Table1[Created Date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
2 REPLIES 2
Super User

Hi,

Try this calculated column formula in Table1

=CALCULATE(MAX(Table2[Name]),FILTER(Table2,Table2[Start Date]<=EARLIER(Table1[Created Date])&&Table2[End Date]>=EARLIER(Table1[Created Date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@manalla Try:

Column =
VAR __Date = 'Table 1'[Created Date]
VAR __Name = MAXX(FILTER('Table2',__Date >= [Start Date] && __Date <= [End Date]),[Name])
RETURN
__Name

Measure =
VAR __Date = MAX('Table 1'[Created Date])
VAR __Name = MAXX(FILTER('Table2',__Date >= [Start Date] && __Date <= [End Date]),[Name])
RETURN
__Name

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors