topic Re: Detect Past Month lost customer in DAX Commands and Tips

Detect Past Month lost customer

APR92 — Tue, 17 Oct 2023 06:32:06 GMT

Hi,

I'm trying to find the customers that I lost from the previous month.

So far I've been able to calculate it individually but I'm stuck with the subtotals.

The model is a single table inndicating if a customer is active or not. The expected outcome is the measure "Lost".

The measure for past months looks like this (works fine):

Past Month =:

VAR var1 =

CALCULATE(

SUM(Consulta1[Active]),

PREVIOUSMONTH(Consulta1[Date])

)

RETURN

var1

The measure for lost looks like this (the subtotals don't work fine):

Lost =

VAR var1 = SUM(Consulta1[Active]) - [Past Month]

RETURN

IF(

var1>=0,

BLANK(),

var1

)

Any idea? Thanks!!

Re: Detect Past Month lost customer

giammariam — Mon, 16 Oct 2023 23:42:19 GMT

Hey APR92 try this. The negatives under the Lost field made it a bit confusing. I made some assumptions with the business rules:

If the prior date was the first date in the dataset, then [Lost] and [Past Month] are BLANK()
If Sum of Active is > [Past Month] then [Lost] = 0
If Sum of Active is <= [Past Month] then [Lost] = [Past Month] - [Active]
- Note, if you want negatives in the [Lost] column, then you can switch this to [Active]-[Past Month]
The Subtotals for [Lost] follow the above rules as well

Let me know if any of these rules are incorrect.

Past Month = VAR var1 = CALCULATE( SUM(Consulta1[Active]), PREVIOUSMONTH(Consulta1[Date]) ) VAR maxDate = MAXX( ALLSELECTED(Consulta1), Consulta1[Date] ) VAR sumMinusMaxDate = SUMX( FILTER( ALLSELECTED(Consulta1), Consulta1[Date] <> maxDate ), Consulta1[Active] ) RETURN IF( COUNTROWS(ALLSELECTED(Consulta1)) = COUNTROWS(Consulta1), sumMinusMaxDate, var1 )

Lost = VAR sumActive = SUM(Consulta1[Active]) VAR var1 = SWITCH( TRUE, ISBLANK([Past Month]), BLANK(), sumActive<=[Past Month], [Past Month]-sumActive, sumActive>[Past Month], 0 ) RETURN var1

If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

Re: Detect Past Month lost customer

APR92 — Tue, 17 Oct 2023 15:00:35 GMT

giammariam Hello, the subtotal for the period 2023-03 is not correct. The user granularity results is fine.

Thank you a lot for your time!

Re: Detect Past Month lost customer

APR92 — Tue, 17 Oct 2023 15:44:21 GMT

I'm super stuck with it. Help please :$

I've played a little bit the past period measure, but the subtotals still wrong...

giammariam 's response has the exact same issue with the lost's subtotals.

Users Lost Past Period =

VAR var1=

CALCULATETABLE (

VALUES ( CALC_userreport_Summarized[user_name] ),

ALL(),

PREVIOUSMONTH(CALC_userreport_Summarized[Date]),

CALC_userreport_Summarized[user_name] = SELECTEDVALUE(CALC_userreport_Summarized[user_name])

)

VAR var2 = COUNTROWS(var1)

VAR var3 =

IF(

var2 - [Users Past Period] = 0,

BLANK(),

var2 - [Users Past Period]

)

RETURN

var3

Re: Detect Past Month lost customer

giammariam — Tue, 17 Oct 2023 16:17:41 GMT

APR92, this is because of the desired behavior was unclear. Overall for 2023-03 you had 4 currently active where you only had 3 active the month before. Since you still ended up with a total of more active than you did the previous month, the [Lost] metric is indicating that overall you didn't lose any for that month. Sounds like instead you want a total of any lost, regardless of what the overall picture is for that month. Now that I know that this is the desired behavior I'll try to get this implemented as soon as I get a chance.

Re: Detect Past Month lost customer

APR92 — Tue, 17 Oct 2023 16:29:47 GMT

giammariam Hello,

First of all apologizes if the logic is not clear. Let me clarify:

I would like to determine how many customers I've lost this month versus the ones that I had the previous month only.

This means that any previous customer I had older than the previous month does't count. The comparison is month by month.

For example, I have to compare the customers that were active in february with the customers that were active in january.

The expected result right now is fine with the user granularity but the montly subtotals are not showing a proper value:

As you can see the subtotal "2023-02" is correct as is adding up user B and E that are lost.

The subtotal 2023-03 is wrong as is 0 and is not adding up the user C that is los (highlighted in green).

PS: Don't worry about the grand total as it is not needed in this measure.

Thank you very much, I highly appreciate your support! 🙂

Re: Detect Past Month lost customer

giammariam — Tue, 17 Oct 2023 20:44:17 GMT

Hey APR92, there is probably a much cleaner way to do this, but try this:

Lost2 = VAR selectedDate = SELECTEDVALUE(Consulta1[Date]) VAR selectedCategory = SELECTEDVALUE(Consulta1[Category]) VAR tbl = ADDCOLUMNS ( ADDCOLUMNS ( Consulta1, "Active Last Month", MAXX ( FILTER ( ALL ( Consulta1 ), Consulta1[Date] < EARLIER(Consulta1[Date]) && ( Consulta1[Category] = EARLIER(Consulta1[Category]) || ISBLANK ( EARLIER(Consulta1[Category]) ) ) ), Consulta1[Active] ) ), "Lost", SWITCH ( TRUE, ISBLANK ( [Active Last Month] ), BLANK (), [Active Last Month] < Consulta1[Active], 0, [Active Last Month] - Consulta1[Active] ) ) VAR lost = SUMX ( FILTER ( tbl, Consulta1[Date] = selectedDate && ( Consulta1[Category] = selectedCategory || ISBLANK ( selectedCategory ) ) ), [Lost] ) RETURN lost