Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I'm new to power bi and dax calculations. I've spent the last few hours trying to solve an issue I'm having with a lost customers formula. The issue is the formula includes some recovered customers in the lost customers list. While the customers were technically lost and recovered in the same year (i.e. one customer went from Q1 2016 until Q2 2017 without a sale, so greater than 365 days), I'm confusing my sales team by including customers in both lists. I've pasted the lost customers formula below. Any and all help would be greatly appreciated!
2 Lost Customers =
COUNTROWS (
FILTER (
ADDCOLUMNS (
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
VALUES (SalesInvoice[Customer Group]),
"CustomerLostDate",
CALCULATE (MAX( SalesInvoice[Date])) + 365),
FILTER (
ALL ( DimDate ),
AND (
DimDate[Date] < MIN (DimDate[Date]),
DimDate[Date] >= MIN (DimDate[Date]) - 365))),
AND (
AND (
[CustomerLostDate] >= MIN ( DimDate[Date]),
[CustomerLostDate] <= MAX ( DimDate[Date])),
[CustomerLostDate] <= CALCULATE ( MAX ( SalesInvoice[Date]), ALL ( SalesInvoice)))),
"FirstBuyInPeriod", CALCULATE (MIN(SalesInvoice[Date]))),
OR (
ISBLANK ([FirstBuyInPeriod]),
[FirstBuyInPeriod] > [CustomerLostDate])))
Bump.
bump.
Can you post some sample/example data? It is very difficult to just look at complex DAX code and see what the issue is, you generally have to mess around with it a bit and for that you need the data.
I added data to the original post.