Forum Discussion

jadewind's avatar
jadewind
Advocate I
9 years ago
Solved

Matching dates

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
jadewind
Advocate I
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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