Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |