Forum Discussion

Jazzed2b's avatar
Jazzed2b
New Member
2 years ago
Solved

Filtering data between two columns from different tables

I have two tables with a ONE to MANY relationship via POLICY NUMBER.  On the ONE table I have a column POLICY EFFECTIVE DATES and on the MANY table I have multiple drivers identified on a DELETEDATE.  I want to filter to count only the number of drivers where the difference betwen POLICY EFFECTIVE DATES and DELETEDATE are less than 365.  The reason being that when a policy expires after one year (365) they are all marked with a DELETEDATE 365 days after the POLICY EFFECTIVE DATE.  I only want to cound the number of drivers which are deleted before the policy expires at 365. 

 

Here is a sample of the table relationships.  I have tried filtering the data based on the relationships, but I do not know if Power BI can subtract the DELETEDATE from the POLICY EFFECTIVE DATE as a condition prior to filter and summing the remaining driver.  Is this something better accomplished as a query on Access DB and then loading the data in Power BI or is there a formula in Power BI to compare the dates on these two columns and then summing the drivers which qualify (meaning having a DELETEDATE less than 365 days from the POLICY EFFECTIVE DATE)? 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Jazzed2b ,

     

    You can try it by following steps:

    1.you can create a calculated column to compute the difference in days

    DaysDifference = DATEDIFF(RELATED(AL Policy Data[EFFECTIVE DATE]), Driver Report[DELETEDATE], DAY)

    2.you can easily filter this table to include only the rows where is less than 365 and then count the number of drivers. This can be done by creating a measure

    DriversCount = COUNTROWS(FILTER(Driver Report,Driver Report[DaysDifference] < 365))

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Jazzed2b ,

     

    You can try it by following steps:

    1.you can create a calculated column to compute the difference in days

    DaysDifference = DATEDIFF(RELATED(AL Policy Data[EFFECTIVE DATE]), Driver Report[DELETEDATE], DAY)

    2.you can easily filter this table to include only the rows where is less than 365 and then count the number of drivers. This can be done by creating a measure

    DriversCount = COUNTROWS(FILTER(Driver Report,Driver Report[DaysDifference] < 365))

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.