Power BI - DAX Measure to calculate churned and reactivated customers in the current period. Incorrect total

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, that's why the date slicer is there and I use an all expect 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 shows the matrix works as expected. Not the totals. I've tried using calculate with distinct count 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

• In order to use SalesData table as a slicer, you will need a date table with no relationship to your SalesData table. The following way will work:

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

• You can read the minimum and maximum dates to get a dynamic period when you use that as a slicer

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 )

• It doesn't solve the complete problem, but you can create a new measure that uses the above

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

• You need to evaluate “ChurnedInPeriod” for each customer separately and that's what “ChurnedCount” exactly 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.

I would recommend this Power BI training by Intellipaat.