Monthly Attrition Chart

DAX DAX Calculations
1

Attrition Model

Hi, I’ve been asked to create the attached chart using our data for monthly sales. I’ve been through the attrition module but am struggling to make any formula work.

Month 1 is all the new customers for that month (I can get that figure). Hence 100%.
Month 2 is how many of the new customers in month 1 paid during this month.
Month 3 is how many of the new customers from month 1 paid during this month etc.

It would work with a MonthInCalendar date filter where you could choose a month and the chart would reflect that month as being month 1.

Thanks

2

@KieftyKids ,

What you are trying to do is basically cohort analysis. @sam.mckay has a number of great videos and showcases on this topic – check out the link below. I think you’ll find it gives you everything you need to solve this problem, but if not please post your PBIX in progress.

https://forum.enterprisedna.co/search?expanded=true&q=%23enterprise-dna-online%20cohort

– Brian

3

Hi Brian

Thank you for pointing me in the right direction - and for your interview on the analytic mind podcast. This forum is a lifeline.

I have developed cohort analysis tables and charts as demonstrated by Sam - and learnt heaps in doing so. (Sample report attached.)

The only problem is that it is not quite what I’ve been asked to report on.

The data is for recurring monthly sales so the cohort grouping has worked perfectly. However, I need to find out how many who had a sale in month 0 (always 100%) then had a sale in month 1, then month 2 etc. That is I’m not looking at when they cancelled, rather how many made a successful sale in the next period.

The success or otherwise of a sale is noted in the Sales table under ‘Sale Status’. A successful sale = ‘Complete’. Every other sale status reason is a failed payment. (Also, customers drop off along the way so won’t have any sales records from the month they drop off.)
Cohort Analysis sample.pbix (2.5 MB)

Please ask questions if my description is vague.

Thanks again.

2 Likes
4

@KieftyKids ,

Sorry for the delay in responding - for the one I had in the queue before yours, @Mike threw a fascinating but really difficult question at me that sent me down a rabbit hole and took a while to resond to. I should have a proposed solution back to you later today.

BTW - thanks for the feedback on the podcast - I had a lot of fun doing that one with @sam.mckay . Also, beautiful job on the cohort analysis. Sorry that didn’t fully address your question, but tons of great skill-building in the replication of that analysis.

  • Brian
5

@KieftyKids ,

If I understand the requirement correctly, I think the solution below should work for you:

image
image788×533 16.4 KB

Here’s how I did it:

  1. created two disconnected dates tables, one for the start date slicer and one for the end date slicer

  2. created the following [In Selected Range] measure to filter out any dates in the bar chart not within the range defined by the slicers:

    In Selected Range =

     IF(
     AND(
         MAX( Dates[MonthnYear] ) >= [Harvest Start Month],
         MAX( Dates[MonthnYear] ) <= [Harvest End Month] 
     ),
     1, 0
 )
  1. Placed that measuring the filter pane, keeping only those months where [In Selected Range] = 1
  2. Created the following measure that does most of the heavy lifting. When you break it up into discrete chunks using variables, it’s pretty straightforward - the denominator is the total number of distinct customer IDs who made a purchase in the selected start month. The numerator is the intersection between the total distinct customer IDs made a purchase in the current row month intersected with the list of customer IDs that form the denominator. The final percentage is just a DIVIDE function of numerator and denominator variables, to protect against a divide by zero situation.

Monthly Stickiness % =

VAR CurrentRowMonth = SELECTEDVALUE( Dates[MonthnYear] )

VAR UniqCustTableStart  =
CALCULATETABLE(
    DISTINCT( Sales[Sale Customer ID]  ),
    FILTER(
        ALLSELECTED(Dates),
        Dates[MonthnYear] = [Harvest Start Month]
    )
)

VAR UniqCustTableCurrent =
CALCULATETABLE(
    DISTINCT( Sales[Sale Customer ID]  ),
    FILTER(
        ALLSELECTED(Dates),
        Dates[MonthnYear] = CurrentRowMonth
    )
)

VAR RetainedCustsFromFirstMoTable = 
INTERSECT( UniqCustTableStart, UniqCustTableCurrent )

VAR Numerator =
COUNTROWS( RetainedCustsFromFirstMoTable )

VAR Denominator =
COUNTROWS( UniqCustTableStart )

VAR Result =
DIVIDE(
    Numerator,
    Denominator,
    BLANK()
)

RETURN Result

I hope this hits the mark. Full solution file attached below.

– Brian
e DNA Forum - Monthly Attrition Chart Solution.pbix (2.6 MB)

6

Hi Brian

Thank you again. I look forward to studying your solution.

7

Hi @KieftyKids, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

8

Definitely check out these videos

Describer exactly how to create cohort analysis

Sam