Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
manalla
Helper V
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.

Any leads will really help.

 

Table 1  Table2  
IDCreated Date NameStart DateEnd Date
16/30/2022 A7/20/20228/2/2022
25/15/2022 B8/3/20228/16/2022
37/25/2022 C8/17/20228/30/2022
48/1/2022 D8/31/20229/20/2022
57/10/2022    
68/10/2022    
      
Output     
IDCreated DateName   
18/30/2022C   
29/10/2022D   
37/25/2022A   
48/1/2022A   
58/28/2022C   
68/10/2022B   

 

Thanks

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.