Power BI - DAX Measure to calculate churned and re

Question

Below is a simplified version of the data. Daily transaction list for customer ID

SalesData = 
DATATABLE (
    "Customer ID", INTEGER,
    "Date", DATETIME,
    "Amount", INTEGER,
    {
         { 101245, "2019/04/07", 500 },
         { 101245, "2018/08/05", 400 },
         { 100365, "2018/07/30", 900 },
         { 100365, "2018/02/22", 700 },
         { 104300, "2019/04/05", 300 },
         { 104300, "2019/04/03", 350 },
         { 104300, "2019/04/01", 310 },
         { 107804, "2018/11/08", 650 },
         { 107804, "2018/11/19", 640 },
         { 108040, "2019/01/02", 730 }
    }
)

Objective: Calculate Reactivated and churned customers during the current period which in the example below is 1-7th April 2019.

Churned = Inactive for 90 days or more.

Reactivated = Inactive for 90 days or more prior to making the latest purchase.

In a matrix - as visualized below - the following measures work as expected for reactivated and churned in the current period, 1st to 7th of April.

    churnedInCurrentPeriod = 
    VAR dayspassed =
    DATEDIFF(
        MAX(SalesData[Date]),
        CALCULATE(
            MAX(SalesData[Date]),
            ALLEXCEPT(SalesData,SalesData[Date])),
            DAY)
    Return 
    IF(dayspassed >= 90 && dayspassed <= 97,1,0)

Please note that the "current period" in this case needs to be dynamic to the date, thats why the date slicer is there and I use an allexpect on the date column to make it work. In the if statement it's 90 + 7 days, should be dynamic this as well.

ReactivatedInCurrentPeriod = 
VAR differenceDays =
DATEDIFF(
    CALCULATE(
        MAX(SalesData[Date]),
        FILTER(SalesData,SalesData[Date] <> MAX(SalesData[Date])
        )
    ),
    MAX(SalesData[Date]),
    DAY
)
RETURN 
IF(AND(differenceDays >= 90,MAX(SalesData[Date]) >= DATE(2019,4,1)),1,0)

As the screenshot show the matrix works as expected. Not the totals. I've tried using calculate with distinctcount to count the customers accordingly without success. Currently I solve this in my real dataset by exporting the matrix and sum in excel(!).

Has to be a better way to make this work with DAX.

Many thanks for the help.

Answer 1

First, you need a Dates table with no relationship to your SalesData table to use as a slicer. The following works well enough for purposes here:

Dates = CALENDAR( DATE( 2018, 1, 1 ), DATE( 2019, 12, 31 ) )

When you use that as a slicer, you can read the max and min dates to get a dynamic period like this:

ChurnedInPeriod =
VAR MaxDate = MAX ( Dates[Date] )
VAR MinDate = MIN ( Dates[Date] )
VAR CustomerLastDate = CALCULATE ( MAX ( SalesData[Date] ), SalesData[Date] <= MaxDate )
VAR DaysPassed = MaxDate - CustomerLastDate
VAR PeriodLength = MaxDate - MinDate
RETURN
    IF ( DaysPassed >= 90 && DaysPassed <= 90 + PeriodLength, 1, 0 )

This doesn't solve the subtotal problem, but you can now write a new measure that uses the above that does:

ChurnedCount = SUMX ( VALUES ( SalesData[Customer ID] ), [ChurnedInPeriod] )

The approach for reactivated accounts should be similar.

The key here is that you need to evaluate ChurnedInPeriod for each customer separately and that's exactly what ChurnedCount does. For each individual customer, it evaluates ChurnedInPeriod for that one and then adds them all together. This SUMX ( VALUES( ... ), ... ) pattern is common for subtotals that need to be rolled up from lower granularity calculations.

---

Here's another question that discusses SUMX ( VALUES ( ... ), ... ) and include further links.