Forum Discussion

APR92's avatar
APR92
Frequent Visitor
2 years ago
Solved

Detect Past Month lost customer

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 inndic...
churn
DAX
lost customer
past month
  • giammariam's avatar
    giammariam
    2 years ago

    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

     

     

APR92's avatar
APR92
Frequent Visitor
2 years ago

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! 🙂

  • giammariam's avatar
    giammariam
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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