Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to calculate our "Customer Lost" date, defined as when a customer hasn't hade a purchase in ( last sales date + [LostDaysLimit] ) - 180 days in our case.
i.e. if a customer later on returns, they should not get a new "Lost Date" but keep their prior one.
I have been trying to modify the attached "Lost Customers" code from @marcorusso, but eventually gave up as I'm still fairly new to dax.
Anyone able to offer advice on how to modify the code to get my desired result?
Thx!
[Lost Customers] := COUNTROWS ( FILTER ( ADDCOLUMNS ( FILTER ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( <customer_key_column> ), "CustomerLostDate", CALCULATE ( MAX ( <fact_date_column> ) ) + [LostDaysLimit] ), FILTER ( ALL ( <date_table> ), AND ( <date_column> < MIN ( <date_column> ), <date_column> >= MIN ( <date_column> ) - [LostDaysLimit] ) ) ), AND ( AND ( [CustomerLostDate] >= MIN ( <date_column> ), [CustomerLostDate] <= MAX ( <date_column> ) ), [CustomerLostDate] <= CALCULATE ( MAX ( <fact_date_column> ), ALL ( <fact_table> ) ) ) ), "FirstBuyInPeriod", CALCULATE ( MIN ( <fact_date_column> ) ) ), OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] ) ) )
BUMP 😕
Basically I know that if I use the the listed measure and filter it by date and customer, I will get a count of 1 on the date the customer was lost.
But instead of 1, i want the formula to return the Lost Date and then only do it once for each customer, which has to be the first Lost Date thinking I could subtract the Lost date and then select the first by using some version of minimum to get my desired result.
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |