Flag when date threshold has been reached

[VinnyH]

Hello,

I'm trying to create a dynamic report to show customer revenue data over a period of 6 months that will be automatically updated moving forward. I would like to show when a customer hasn't traded with us for a three month period and for this to be automatically flagged in the report. 

I've seen messages about using a measure to flag when a value threshold is reached on other threads, but cannot work this out when it comes to dynamic date ranges.

Any help would be greatly appreciated.

[edhans · Accepted solution]

This will calculate how many days since the last sale happened. This assumes a Date table is set up.

Days Since Last Sale = 
CALCULATE(
    DATEDIFF(MAX(Sales[Date]),TODAY(),DAY),
    ALL('Date'[Date])
)

It assumes your fiter context in the table has customers in it. In my sample data, the last sale for Customer 6 was May 15, 2019, so that was 93 days ago. You could then do whatever you want when that hits 90+ days.

[VinnyH]

That's absolutely fantastic, thank you. I would've been here forever trying to work that out!