Forum Discussion

everyrichrole's avatar
everyrichrole
Frequent Visitor
8 years ago
Solved

Check if record from table A falls within a date range in table B

I have a table that I'll call Event.   Event date person_id   I have another table called Biography.   Biography start_date end_date person_id title   Every Event row should be ...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this calculated column in the Events Table

     

    =CALCULATE(FIRSTNONBLANK(Biogaphy[title],1),FILTER(Biography,Biography[start_date]<=EARLIER(date)&&Biography[end_date]>=EARLIER(date)&&Biography[person_id]=EARLIER(person_id)))

     

    If this formula does not work, then share the link from where i can download your PBI file.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Try this calculated column in the Events Table

 

=CALCULATE(FIRSTNONBLANK(Biogaphy[title],1),FILTER(Biography,Biography[start_date]<=EARLIER(date)&&Biography[end_date]>=EARLIER(date)&&Biography[person_id]=EARLIER(person_id)))

 

If this formula does not work, then share the link from where i can download your PBI file.

everyrichrole's avatar
everyrichrole
Frequent Visitor
8 years ago

That worked. Thank you! I guess the EARLIER function was what I needed.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    You are welcome.