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.