Forum Discussion
Flag when date threshold has been reached
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.
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.
2 Replies
- edhans
Community Champion
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.
- That's absolutely fantastic, thank you. I would've been here forever trying to work that out!
