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 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.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |