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

[everyrichrole]

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 associated with one and only one Biography entry (but a single person_id may have multiple Biography entries associated with it, i.e., George Washington: The Early Years, and George_Washington: The Later Years can be separate Biography entries, but they must cover non-overlapping years). I want to add a calculated column to the Event table that will hold the title of the Biography entry to which it corresponds by checking that the person_id is equal and that the Event date falls between the start_date and end_date of the Biography entry.

How do I do this? I've tried a number of formulas but can't seem to get it quite right.

[Ashish_Mathur · Accepted solution]

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]

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

[Ashish_Mathur]

You are welcome.