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


#62

Another question:
Result 2003-1: the Total Clients Prior Period -1M and Lost Clients LTM gives both a result but there are no transactions in this period. What can I do to get rid of these results? :slight_smile:

Chrs, Cor


#63

It was just this one

Test = DATEADD(LASTDATE(‘Calendar’[Date]),-1,MONTH)

I broke it out from the initial formula to see if it was getting the correct date bounds.


#64

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

image


#65

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

#66

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


#67

image

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:

  1. I am expecting the result to be 8 or very close, and am getting 707 for the year.
  2. 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


#68

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


#69

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


#70

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


#71

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


#72

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


#73

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


#74

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…


#75

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

image


#76

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?


#77

Ok sure,

How about this then

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


#78

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)


#79

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


#80

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 )

image

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)


#81

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