I know this should be really straightforward, but cannot figure this out. I am wanting to create a rolling "within last 7 days" TRUE/FALSE column in Power BI within my Data Dimension table to allow me to use this as a filter for other calculations - this will be based on the latest data run date in my Fact table (which can be delayed until the afternoon every day it is ran, hence why I am not just simply using the TODAY() function).
I have tried to use the DATEDIFF function when creating the column, which works fine when I test this within my fact table (as the MAX run date is the last run date in the table). However, when referencing my Date Dimension table, it gives me TRUE values for everything up to 7 days prior to the Latest Run Date', but also for every date in the future past the Latest Run Date. My Date Dimensioning table has dates for the next 5 years, hence why this is an issue.
Rolling 7 Days = if (DATEDIFF('Fact Table'[Date], MAX('Fact Table'[Date]), DAY) <= 7, TRUE(), FALSE())
How can I re-write this so that it explicitly gives a TRUE result for ONLY the last 7 days from the latest data run date so I can use it as a filter?
Could this even be wrote as a measure?
Thanks in advance!
Try using two conditions instead of just one.
For example,
Rolling 7 Days =
VAR MaxFactDate = MAX ( 'Fact Table'[Date] )
RETURN
( dimDate[Date] > MaxFactDate - 7 ) && ( dimDate[Date] <= MaxFactDate )