Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I want to calculate the number of days between today and the last time a customer made a purchase order. I have a table with the purchase order nr as key and then the customer nr and purchase date. For every customer I want to know how long it has been since his last puchase. I tried to make a measure with the datedifference function but it never gives the correct result. Does anybody knows how to do this? 🙂
Thanks in advance!
Solved! Go to Solution.
Calculate Max Order Date Per Customer using ALLEXCEPT and then calculate Datediff with today.
Time Since Last Order Date =
Var LastOrderDt = CALCULATE(MAX('P6:TimeSinceLastOrder'[OrderDate]), ALLEXCEPT('P6:TimeSinceLastOrder','P6:TimeSinceLastOrder'[CustomerId]))
Var TimeSinceLastOrderDt = DATEDIFF(LastOrderDt,TODAY(),DAY)
RETURN TimeSinceLastOrderDt
Calculate Max Order Date Per Customer using ALLEXCEPT and then calculate Datediff with today.
Time Since Last Order Date =
Var LastOrderDt = CALCULATE(MAX('P6:TimeSinceLastOrder'[OrderDate]), ALLEXCEPT('P6:TimeSinceLastOrder','P6:TimeSinceLastOrder'[CustomerId]))
Var TimeSinceLastOrderDt = DATEDIFF(LastOrderDt,TODAY(),DAY)
RETURN TimeSinceLastOrderDt
Create this measure:
Days Since Last Order = DATEDIFF(MAX(Sales[OrderDate]), TODAY(), DAY)
Then use it in a table with the customer key or name
This does not seems to work if I try to adjust the current date
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |