cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Lost Customers - Also counting recovered customers

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])))

 

 

Capture.JPG

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Lost Customers - Also counting recovered customers

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Lost Customers - Also counting recovered customers

I added data to the original post.

Highlighted
Frequent Visitor

Re: Lost Customers - Also counting recovered customers

bump.

Highlighted
Frequent Visitor

Re: Lost Customers - Also counting recovered customers

Bump.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors