Matching dates

[jadewind]

I have two tables, one showing people's IDs, Locations and Start Date and End Date of people's locations , the other one showing incidents with Incident Date and people's IDs. 

I want to find out where the people were when the incidents happened. So it's about matching the Incident Date with the Start/End Date to get the Location.

How can I achieve this in Power BI?

[v-yuezhe-msft]

Hi @jadewind,

Do you want to get the location when Incident Date equals to the Start Date or End Date? If that is the case, I make a test using the following two tables.

In Table5, you can just create a calculated column using the formula below, then create a table visual using the calculated column and other fields.

GetLocation = CALCULATE(FIRSTNONBLANK(Table4[Locations],Table4[Locations]),FILTER(Table4,(Table4[Start Date]=Table5[Incident Date]||Table4[End Date]=Table5[Incident Date])&&Table4[ID]=Table5[ID]))

However, if you want to get the location when Incident Date is between Start Dare and End Date, just create a calculated column using the formula below, then create a table visual using the calculated column and other fields.

GetLocation1 = CALCULATE(FIRSTNONBLANK(Table4[Locations],Table4[Locations]),FILTER(Table4,Table4[Start Date]<=Table5[Incident Date]&&Table5[Incident Date]<=Table4[End Date]&&Table4[ID]=Table5[ID]))

Thanks,
Lydia Zhang

[jadewind]

Or I think the bit after <= has to be a measure not a column?

[v-yuezhe-msft · Accepted solution]

Hi  @jadewind，

I haven't created ant relationship between the two tables. You can check the attached PBIX file to get more details about how to create the calculated column.

Thanks,
Lydia Zhang

[jadewind]

Thanks Lydia for trying to help. However your formula does not work when I started the second row. I think it has something to do with the relationship between two tables. I cannot link the two tables directly because the ID fields in both tables have duplicates - meaning each ID can have multiple locations through different dates, and each ID can have multiple incidents. However each incident has a unique Incident No. 

Not sure how I can upload a sample xlsx document but I have attached a couple of snapshots below. One table contains the incident dates, the other table contains the locations data. I have tried linking the two tables using another Date or ID table but still couldn't use your formula (eg. the formula wouldn't recoganize the "Incident Date" field). 

Do you have any suggestions?