Filtering data between two columns from different tables

[Jazzed2b]

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 · Accepted solution]

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.

[amitchandak]

@Jazzed2b , as they have on to many relation

a new column in diver report table

=  datediff(related('AL Policy Data'[Effective Date]), 'Diver Data'[deleted date], date)

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8