DAX rolling churn for the month (MTD)

[wyanjaspew]

Hi guys,

I’d like to ask for your assistance in getting the correct output.

The goal is to calculate a rolling daily count of churned services. Appreciate your help in advance.

SUMMARIZE(

    ADDCOLUMNS(

        churn_services,

        "Date", churn_services[disconnectiondate],

        "Customer Type", churn_services[customertypename],

        "Measures", "MTD Churn",

        "Product Category", churn_services[productcategoryname],

        "UB Group", churn_services[groupname],

        "Total Churn Services",

            CALCULATE(

                DISTINCTCOUNT(churn_services[serviceid]),

                FILTER(

                    ALLEXCEPT(

                        churn_services,

                        churn_services[customertypename],

                        churn_services[productcategoryname],

                        churn_services[productitemname],

                        churn_services[groupname]

                    ),

                    churn_services[disconnectiondate] <= MAX(churn_services[disconnectiondate]) &&

                    MONTH(churn_services[disconnectiondate]) = MONTH(TODAY()) &&

                    YEAR(churn_services[disconnectiondate]) = YEAR(TODAY())

                )

            )

    ),

    [Date],

    [Measures],

    [Customer Type],

[v-hashadapu]

Hi @wyanjaspew , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @wyanjaspew , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[v-hashadapu]

Hi @wyanjaspew , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[rohit1991]

Hi @wyanjaspew

Try this :
DAX (Calculated Table):

Churn_MTD_Table = 
VAR BaseTable =
    ADDCOLUMNS(
        'Table',
        "Date", 'Table'[disconnectiondate],
        "Customer Type", 'Table'[customertypename],
        "Measures", "MTD Churn",
        "Product Category", 'Table'[productcategoryname],
        "UB Group", 'Table'[groupname],
        "Total Churn Services",
            CALCULATE(
                DISTINCTCOUNT('Table'[serviceid]),
                FILTER(
                    ALLEXCEPT(
                        'Table',
                        'Table'[customertypename],
                        'Table'[productcategoryname],
                        'Table'[productname],
                        'Table'[groupname]
                    ),
                    'Table'[disconnectiondate] <= EARLIER('Table'[disconnectiondate])
                        && MONTH('Table'[disconnectiondate]) = MONTH(EARLIER('Table'[disconnectiondate]))
                        && YEAR('Table'[disconnectiondate]) = YEAR(EARLIER('Table'[disconnectiondate]))
                )
            )
    )
RETURN
SELECTCOLUMNS(
    BaseTable,
    "Date", [Date],
    "Customer Type", [Customer Type],
    "Measures", [Measures],
    "Product Category", [Product Category],
    "UB Group", [UB Group],
    "Total Churn Services", [Total Churn Services]
)

[wyanjaspew]

still not working sir

[rohit1991 · Accepted solution]

Hi @wyanjaspew 

Try this:

Churn_MTD_Table = 
VAR BaseTable =
    ADDCOLUMNS(
        'Table',
        "Date", 'Table'[disconnectiondate],
        "Customer Type", 'Table'[customertypename],
        "Measures", "MTD Churn",
        "Product Category", 'Table'[productcategoryname],
        "UB Group", 'Table'[groupname],
        "Total Churn Services",
            CALCULATE(
                DISTINCTCOUNT('Table'[serviceid]),
                FILTER(
                    ALLEXCEPT(
                        'Table',
                        'Table'[customertypename],
                        'Table'[productcategoryname],
                        'Table'[productname],
                        'Table'[groupname]
                    ),
                    'Table'[disconnectiondate] <= EARLIER('Table'[disconnectiondate])
                        && MONTH('Table'[disconnectiondate]) = MONTH(EARLIER('Table'[disconnectiondate]))
                        && YEAR('Table'[disconnectiondate]) = YEAR(EARLIER('Table'[disconnectiondate]))
                )
            )
    )
RETURN
SELECTCOLUMNS(
    BaseTable,
    "Date", [Date],
    "Customer Type", [Customer Type],
    "Measures", [Measures],
    "Product Category", [Product Category],
    "UB Group", [UB Group],
    "Total Churn Services", [Total Churn Services]
)

Output: 

[Azadsingh]

hi @wyanjaspew , Please try this:

Table = SUMMARIZE(ADDCOLUMNS(churn_services,"Date", churn_services[disconnectiondate],"Customer Type", churn_services[customertypename],"Measures", "MTD Churn","Product Category", churn_services[productcategoryname],
"UB Group", churn_services[groupname],
"Product Item", churn_services[productitemname],
"Total Churn Services",
CALCULATE(
DISTINCTCOUNT(churn_services[serviceid]),
FILTER(
ALLEXCEPT(
churn_services,
churn_services[customertypename],
churn_services[productcategoryname],
churn_services[productitemname],
churn_services[groupname],
churn_services[disconnectiondate]
),
churn_services[disconnectiondate] <= EARLIER(churn_services[disconnectiondate]) &&
MONTH(churn_services[disconnectiondate]) = MONTH(TODAY()) &&
YEAR(churn_services[disconnectiondate]) = YEAR(TODAY())
)
)
),
[Date],
[Measures],
[Customer Type],
[Product Category],
[UB Group],
[Product Item]
)

Regards,

Azad

[wyanjaspew]

Still not working

[wardy912]

Hi @wyanjaspew 

 For a true rolling count, we need to remove MONTH and YEAR from your DAX. We should also use EARLIER to compare each rows disconnection date against the current row context. Try the following:

SUMMARIZE(
    ADDCOLUMNS(
        churn_services,
        "Date", churn_services[disconnectiondate],
        "Customer Type", churn_services[customertypename],
        "Measures", "Rolling Daily Churn",
        "Product Category", churn_services[productcategoryname],
        "UB Group", churn_services[groupname],
        "Total Churn Services",
            CALCULATE(
                DISTINCTCOUNT(churn_services[serviceid]),
                FILTER(
                    ALLEXCEPT(
                        churn_services,
                        churn_services[customertypename],
                        churn_services[productcategoryname],
                        churn_services[productitemname],
                        churn_services[groupname]
                    ),
                    churn_services[disconnectiondate] <= EARLIER(churn_services[disconnectiondate])
                )
            )
    ),
    [Date],
    [Measures],
    [Customer Type],
    [Product Category],
    [UB Group]
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[wyanjaspew]

Still not good