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 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?

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

4 Replies

  • 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

      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?

    • 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