Comparing New vs Lost vs Steady Customers over Multiple Years - Customer Churn Examples - Advanced DAX

Thanks for your reply Sam, in future I will start a new topic.

I’ve changed a few measures:

MaxWeekNumberTY = 
CALCULATE( MAX( 'Calendar'[WeekOfYearISO] );
    ALL( 'Calendar' ); VALUES( 'Calendar'[ISO Year] ) )

MaxWeekNumberLY = 
CALCULATE( [MaxWeekNumberTY]; DATEADD( 'Calendar'[FullDate]; -1; YEAR ) )

First Date Current Period = 
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[ISO Year] )

RETURN
CALCULATE( [First Date - Current Week];
    FILTER( ALL( 'Calendar' );
        'Calendar'[WeekOfYearISO] = CurrentWeek + 1 &&
        'Calendar'[ISO Year] = CurrentYear - 1 ) )

and I’ve made a new one:

FirstDate ISO Year = CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
    FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1);VALUES('Calendar'[ISO Year]))

As you can see in the next photo, we are still missing the First Date Current Period from 2004W52 and 2004W53 and the First Date Prior Period from 2004W53.

I understand that the result of the measure for 2004W52 and 2004W53 are blank because we haven’t got the weeks 2003W53 and 2003W54:

First Date Current Period = 
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[ISO Year] )

RETURN
CALCULATE( [First Date - Current Week];
    FILTER( ALL( 'Calendar' );
        'Calendar'[WeekOfYearISO] = CurrentWeek + 1 &&
        'Calendar'[ISO Year] = CurrentYear - 1 ) )

For the missing dates I want the result of the measure

FirstDate ISO Year = CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
    FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1);VALUES('Calendar'[ISO Year]))

How can I get the result of FirstDate ISO Year in the measures First Date Current Period and First Date Prior Period?

Chrs,

Cor

Attached
181015 YH Churn per week -1W Demo Data Sam en Cor.pbix (1.1 MB)

Okay getting closer I think. Will just have to look at this closer again, but don’t believe it’s to far from where things are at.

Can you wrap the initial function in an IF statement

Logic would work like this.

IF( Week Number is > 53, Initial Measure, Alternate Measure)

Something like this? Would it be suitable for those last remaining blanks?

Hi Sam,

I’ve changed the following measures:

First Date Current Period = 
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE('Calendar'[ISO Year])
VAR MaxWeekNumber = CALCULATE(max('Calendar'[WeekOfYearISO] ); ALL('Calendar') )

RETURN
IF(ISBLANK(CALCULATE([First Day of the week];
    FILTER(ALL('Calendar');
        'Calendar'[WeekOfYearISO] = VALUES('Calendar'[WeekOfYearISO]) + 1 && 'Calendar'[ISO Year] = VALUES( 'Calendar'[ISO Year]) -1) ));
        CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
             FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1); VALUES('Calendar'[ISO Year]));
                  CALCULATE([First Day of the week];
                    FILTER(ALL('Calendar');
                    'Calendar'[WeekOfYearISO] = VALUES('Calendar'[WeekOfYearISO]) + 1 && 'Calendar'[ISO Year] = VALUES( 'Calendar'[ISO Year]) -1)) )

and

First Date Prior Period = 
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[ISO Year] )

RETURN
IF(ISBLANK(CALCULATE( [First Day of the week];
    FILTER( ALL( 'Calendar' );
        'Calendar'[WeekOfYearISO] = CurrentWeek &&
        'Calendar'[ISO Year] = CurrentYear - 1 ) ) );
            CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
                FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1); VALUES('Calendar'[ISO Year]));
                    CALCULATE( [First Day of the week];
                         FILTER( ALL( 'Calendar' );
                            'Calendar'[WeekOfYearISO] = CurrentWeek &&
                            'Calendar'[ISO Year] = CurrentYear - 1 )))

with the result:

181016%20Sam

I think that we’ve managed to fill the blank values, I can use these dates to calculate the churn by week.
Thanks for pushing me the right way, Sam! :smile:

Greetings from The Netherlands,

Cor

Brilliant! Glad that we got there. Was a tricky one.

181026 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2.pbix (689.2 KB)

Hi Sam,

See attachment above, I have some difficulties with the differences between the calculations of the “Lost Clients per Product” and the “Won Clients per Product”. The Grand Total of those 2 calculations are correct, if you add the several amounts per product, you will get another total. How can I solve this problem?

Furthermore I want to use the Lost Clients LTM, Lost Revenue Clients LTM, Won Revenue Clients LTM and the Won Clients LTM as a slicer. Can you tell me how I can fix that?

I’ve seen a lot of your videos on segmentation, grouping etc. but I can’t find the technique.

Thanks in advance,

With kind regards,

Cor

I’ve tried it with a calculated column in another datamodel but I’ve got the message that there was no memory left…

Customer Group = 
SWITCH(TRUE();
NOT(ISBLANK([Total Sales Prior Period]) && ISBLANK([Total Sales LTM]));"Lost Clients";
ISBLANK([Total Sales Prior Period]) && NOT(ISBLANK([Total Sales LTM]));"Won Clients";
NOT(ISBLANK([Total Sales Prior Period])) && NOT(ISBLANK([Total Sales LTM])) && [Diff. LTM vs PP] < 0;"Lost Revenue";
NOT(ISBLANK([Total Sales Prior Period])) && NOT(ISBLANK([Total Sales LTM])) && [Diff. LTM vs PP] > 0;"Won Revenue";
NOT(ISBLANK([Total Sales Prior Period])) && NOT(ISBLANK([Total Sales LTM])) && [Diff. LTM vs PP] = 0;"Steady Revenue";
BLANK() )

The table YH-invoices of the other model contains more than 3,5 million rows.

In the meantime I’ve found a solution for the slicers with the churn-measures but I still have the miscalculations on the won and lost clients. Can you tell me what I’m doing wrong?

181026 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2.pbix (721.6 KB)

Hi Sam,

I think the problem for Won Clients for 2015W27 it is in customer 30, product 15.
The customer is a customer with Won Revenue instead of Won Clients, the product is a “Won Product”, it wasn’t bought in the prior period. I don’t understand why this is. Can you explain it to me and help me to solve this problem?

181026 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2.pbix (725.3 KB)

Hi Cor,

Sorry I’m a bit confused on this after a few posts in this thread.

Can we please start a new thread on this and be very specific about what you require. Point to specific formulas or results that you’re not sure about.

This has become a quite a complicated model and having to go through it every time over the last few months takes a lot of time.

I would like to be very specific around what you need here so I can look at just this and give you a solution.

Thanks

I think where the confusion lies is your talking about won client and lost clients then in the picture and when I download the file I see different calculations.

Total Sales LTM
Total Sales Prior Period
etc

Sorry it just need to be clearer for me. I want to be able to look at this and understand it pretty quick without having to revisit every detail about the model everytime I download it.

Appreciate it.

As mentioned let’s start a new thread on this.

Thanks
Sam

Sam, I will start a new thread on this. :slight_smile: