New Customer analysis Customer Revenue Lost is not clear

DAX DAX Calculations
1

Hi Fellow Power BI Users -

Back again to new customer analysis scenario, I got confused on the numbers presented in the following 2 images:

New%20Customer
New Customer.JPG2611×936 168 KB

New%20Customer-2
New Customer-2.JPG2623×977 169 KB

In the most right table the customer “Aaron Carr” total sales = 35,680 , and when we calculate the total on the second table for column (Customer Revenu Lost) I understand how it goes by doing accumulated summing for the previous purchases until we reach the highlighted numbers in (yellow) on the second picture where the total sum = 12,087 the next line (Augast 2017) should be 12,087+5,697 = 17,784 but the number calculated in August is 13,302 from here and on the lines on word (till Dec 2017) I need to understand how this numbers calculated ?

2

Can you please add the file you’re looking at so I can review exactly how you have this set up.

This is quite complex so I want to see exactly what your looking at and the formulas there are feeding into them.

Thanks

3

Thank You So much , Please go to page 3 of the file and press on the customer (Aaron Carr) and make the days parameter to consider the customer lost as (0) to get the same case on the orignal post , here is the file link:
https://drive.google.com/open?id=1dTtmEcP17dqWyvWjDi8-X6VGwwQzW8Le

4

It’s important to review the formula creating this results in detail. This is obviously a complex one so there’s plenty to review.

See below

Customer Revenue Lost = 
VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                            FILTER( ALL( Dates ),
                                Dates[Date] >  MIN( Dates[Date] ) - 365 && 
                                Dates[Date] <=  MIN( Dates[Date] ) - [Churn Time Frame Value] ) )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                        FILTER( ALL( Dates ),
                            Dates[Date] >  MAX( Dates[Date] ) - [Churn Time Frame Value] &&
                            Dates[Date] <= MAX( Dates[Date] ) ) )
RETURN
CALCULATE( 
    CALCULATE( [Total Sales],
    DATESBETWEEN( Dates[Date], MIN( Dates[Date] ) - 365, MIN( Dates[Date] ) - [Churn Time Frame Value] ) ) ,
        EXCEPT( CustomersPurchased, PriorCustomers ) ) * -1

We are calculating here the amount lost over the last year only.

image
image.png1130×403 32.6 KB

You’ll see in the image on July 2017, there’s was new sales of 5697. But then July 2016 sales of 4482 dropped off.

That’s why you get the 13302 amount.

Then would be the same all the way down for that calculation.

5

Cool , Thank you Sam I understood now , But keep monitor my post if I find something need to be answered , thank you again.