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

sam.mckay · September 25, 2018, 9:18pm

Something like this should do it

Total Clients Prior Period -1M = 
IF( ISBLANK( [Total Clients LTM] ), 
    BLANK(),
        CALCULATE([Total Clients LTM], DATEADD('Calendar'[Date],-1,MONTH)) )

adamhorowitz · September 26, 2018, 3:00pm

Hi Sam,

How can the below Lost Customers measure be modified to look back 4 years instead of 1 year? The logic being sales in the last 4 years but not this year.

Customer Lost = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
IF( ISBLANK( [Customer Sales TY] ),
    BLANK(),
        COUNTROWS( EXCEPT( CustomerLY, CustomersTY ) ) )

My current measure below is not producing the right results.

 Lost Customers L4 = 
var TY= VALUES('CompleteSalesSummary_Sales'[Rep])
var L4= CALCULATETABLE(VALUES('CompleteSalesSummary_Sales'[Rep]),
    FILTER(ALL(DateTable),
        DateTable[Date]>MIN(DateTable[Date])-1460 &&
        DateTable[Date]<MIN(DateTable[Date])))
     return COUNTROWS(EXCEPT(L4,TY))

sam.mckay · September 26, 2018, 8:24pm

The formula looks ok to me from my review.

What is incorrect about the results?

Can you show what you are seeing and what you think it should be?

You could use this formula as the filter which might be more exact than just days

DATEADD(STARTOFMONTH('Calendar'[Date]),-4,YEAR),
                         LASTDATE( DATEADD('Calendar'[Date],-1,MONTH) )))

See how you go with this variation also

adamhorowitz · September 26, 2018, 9:54pm

Sorry I am new to DAX, but where are you inserting that formula because I get blank answers when I try to put it in my measure. I know the New Customers measure is correct because the 281 checks out with a tedious excel process to arrive at new customers for 2018.

There are 2 issues with my Lost Customers L4:

I am expecting the result to be 8 or very close, and am getting 707 for the year.
Unlike the New Customers measure, this measure is returning 1204 for future months which is unexpected.

Any other ideas on capturing lost customers as the count of customers in 2014-2017 with sales and not in 2018?

Thanks in advance,
Adam

sam.mckay · September 26, 2018, 11:08pm

Ok thanks for this.

Lost customers is always a little tougher. Main reason for this is because how you determine a lost customer can vary widely.

Let’s walk through your existing formula and see if this is what you were wanting.

Lost Customers L4 = 
var TY= VALUES('CompleteSalesSummary_Sales'[Rep])
var L4= CALCULATETABLE(VALUES('CompleteSalesSummary_Sales'[Rep]),
    FILTER(ALL(DateTable),
        DateTable[Date]>MIN(DateTable[Date])-1460 &&
        DateTable[Date]<MIN(DateTable[Date])))
     return COUNTROWS(EXCEPT(L4,TY))

Currently at every single row in your table you are comparing a list of customers who purchased in that particular month to a list of customers that purchased at anytime 4 years prior to that month.

Then by going EXCEPT(L4,TY) you are saying show me a list of customers who purchased in the last 4 years but didn’t purchase this month.

This is why you are getting quite large numbers.

Looking at this again it doesn’t really feel like this is what you’re going for with your lost customers.

What should the logic be?

Check out the lost customers formula here (and logic). It’s quite different. This might get you thinking about what you need a little more.

Here’s more detailed session I ran through new and lost customers also.

Let me know the outcome

CorvanDalfzen · October 8, 2018, 12:47pm

Hi Sam,

After the calculations of the churn by year, quarter and month I’ve received the question to calculate the churn Last Twelve Months (LTM) by week…

For the same demo data ( ‘2003-2004 Invoices’) I have expanded the table Calendar with “FirstDateOfWeek” and “LastDateOfWeek”:

I’m struggling with the periods. Can you help me with the periods LTM and the prior period?

For example:

CalendarYearWeekISO: 2004-W02;
Weeks LTM = 2003-W03 - 2004-W02
Dates LTM = 13-01-2003 - 11-01-2004

Prior Period LTM-1W:
Weeks Prior Period = 2003-W02 - 2004-W01
Dates Prior Period = 06-01-2003 - 04-01-2004

I’ve tried to break the formulas to get the correct date bounds but I didn’t get it. For weekly comparison how do I have to break down the formulas?

Thanks in advance,

Cor

CorvanDalfzen · October 8, 2018, 1:55pm

Hi Sam,

I’ve made some measures and they seem to do what I want:

My measures:

FirstDate LTM -1W = 
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
        'Calendar'[WeekOfYearISO] - 1= max ('Calendar'[WeekOfYearISO])
    )
)

LastDate TW = LASTDATE ( 'Calendar'[LastDateOfWeek])

FirstDate PP LTM -1W = 
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
        'Calendar'[WeekOfYearISO] = max ('Calendar'[WeekOfYearISO])
    )
)

LastDate PP LTM - 1W = DATEADD(LASTDATE('Calendar'[LastDateOfWeek]);-7;DAY)

Is this also your solution or do you have a better solution?

Can you also help me to put these measures to churn-measures like Won/Lost Clients and revenues?

Thanks in advance,

Chrs,

Cor

CorvanDalfzen · October 9, 2018, 2:28pm

Hi Sam,

I have some difficulties with the year-breaks, especially the CalendarYearWeekISO 52 and 53 .

The measure

FirstDate LTM -1W = 
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
        'Calendar'[WeekOfYearISO] - 1= max ('Calendar'[WeekOfYearISO])
    )
)

doesn’t seem to give the correct result so I’ve made another measure:

FirstDate LTM -1W TEST = 
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNumber = CALCULATE(max('Calendar'[WeekOfYearISO] ); ALL('Calendar') )
RETURN
CALCULATE([FirstDate TW];
    FILTER(ALL('Calendar');
        IF( CurrentWeek = 52;
           'Calendar'[WeekOfYearISO] = CurrentWeek -51 && 'Calendar'[Year] = CurrentYear;
           IF( CurrentWeek = 53;
           'Calendar'[WeekOfYearISO] = CurrentWeek -52 && 'Calendar'[Year] = CurrentYear;
           'Calendar'[WeekOfYearISO] = CurrentWeek + 1 && 'Calendar'[Year] = CurrentYear -1))))

and this measure is also not the solution:

Week_Wrong

Can you help me with the correct formula?

Thanks in advance,

Chrs,
Cor

sam.mckay · October 10, 2018, 2:42am

Probably going to have to see an example file again. There’s a lot to this again. Not the easiest to do without seeing and example and testing it a bit more.

Thanks

CorvanDalfzen · October 10, 2018, 6:51am

Hi Sam, I’ve sent you the example file by mail.

CorvanDalfzen · October 10, 2018, 9:38am

The problem occurs in the ISO-weeks 52 and 53. A year with 52 weeks has 52 * 7 = 364 ISO-days, a year with 53 weeks (like 2004, 2009, 2015, 2020 etc.) has 53 * 7 = 371 ISO-days. As an attachment you will find an Excel-file with different calculations.

I think that probably the solution for the FirstDateLTM-measure is:
If weeknumber = 52 and the max weeknumber of last year = 52 then FirstDate Weeknumber 1 of Current Year,
if weeknumber = 52 and the max weeknumber of last year = 53 then FirstDate Weeknumber 53 of Last Year,
if weeknumber = 53 then FirstDate Weeknumber 1 of Current Year, but correct me if i’m wrong.

I don’t know how to fit the probable solution into a Dax-measure. Can you help me?

Chrs,

Cor
Hulpmiddel week 52 en week 53.xlsx (79.1 KB)

PS:
The measure FirstDate PP LTM - 1 W goes also wrong, see 2005W52, the result is 26-12-2005 and this should be 20-12-2004…

sam.mckay · October 11, 2018, 12:05am

Bit confused on this one.

Any reason why you can’t just use this?

FirstDate LTM -1W = 
CALCULATE([FirstDate TW],
    DATEADD( 'Calendar'[FullDate], -7, DAY ) )

CorvanDalfzen · October 11, 2018, 6:07am

Because I don’t want to know the date of 1 week prior, I want to know the firstdate of the prior week one year back:

To solve the challenge with week 53, maybe we should take the dates from week 52 and put them also on week 53. What do you think?

sam.mckay · October 11, 2018, 11:32pm

Ok sure,

How about this then

FirstDate LTM -1W = 
CALCULATE(
    CALCULATE([FirstDate TW],
        DATEADD( 'Calendar'[FullDate], -7, DAY ) ),
            DATEADD( 'Calendar'[FullDate], -1, YEAR ) )

CorvanDalfzen · October 12, 2018, 10:46am

Hi Sam,

Thanks for your reply Sam but unfortunately that isn’t the solution I’m looking for. For me it’s very complex.

See attachment, worksheet explanation.

I know, it looks like mathematics for me and I think also for you. I hope you will take the time to help me with DAX.

Thanks in advance,

Cor

Hulpmiddel week 52 en week 53.xlsx (82.9 KB)

sam.mckay · October 15, 2018, 3:56am

Ok will review again. Sorry I haven’t really been understanding the true problem up to this point.

sam.mckay · October 15, 2018, 7:11am

Ok this is kind of doing my head in.

I’ve look at this for a long time today and I’m up to a certain point and I’m hoping you can take it the next step.

It’s just difficult to get my head around every nuance with this calculation.

But…I do know why this was difficult and I have shown you how to fix it.

The key here is the difference between the calendar year and ISO year.

As these don’t align then the calculation in it’s current form was never going to work.

So what I worked out to do is create another calculated column in the calendar table for ISO year.

ISO Year = LEFT( 'Calendar'[CalendarYearWeekISO], 4 )

Then I have integrated this back into the formulas as it then works through correctly based on the context within the table in the report

I actually have most of your example correct, but then really don’t understand some of the others around the 53 weeks concept.

But this is how you solve what you need.

I think you should be able to work it out from here.

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

sam.mckay · October 15, 2018, 7:12am

Just another note for future before I forget.

Can we please move new topic into new forum posts.

This one is a little different from the initial topic.

I like to break these out so they can be easily tracked and found at a later point.

Thanks

CorvanDalfzen · October 15, 2018, 1:17pm

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)

sam.mckay · October 16, 2018, 8:10am

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?