topic Re: DAX rolling churn for the month (MTD) in DAX Commands and Tips

DAX rolling churn for the month (MTD)

wyanjaspew — Tue, 05 Aug 2025 07:15:39 GMT

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],

Re: DAX rolling churn for the month (MTD)

wardy912 — Tue, 05 Aug 2025 08:11:20 GMT

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!

Re: DAX rolling churn for the month (MTD)

Azadsingh — Tue, 05 Aug 2025 08:43:01 GMT

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

Re: DAX rolling churn for the month (MTD)

rohit1991 — Tue, 05 Aug 2025 13:17:14 GMT

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] )

Re: DAX rolling churn for the month (MTD)

wyanjaspew — Tue, 05 Aug 2025 12:15:30 GMT

still not working sir

Re: DAX rolling churn for the month (MTD)

rohit1991 — Tue, 05 Aug 2025 13:18:13 GMT

Hi wyanjaspew

Try this:

Output:

Re: DAX rolling churn for the month (MTD)

wyanjaspew — Tue, 05 Aug 2025 12:49:41 GMT

Still not working

Re: DAX rolling churn for the month (MTD)

wyanjaspew — Tue, 05 Aug 2025 12:53:56 GMT

Still not good

Re: DAX rolling churn for the month (MTD)

v-hashadapu — Mon, 11 Aug 2025 10:43:57 GMT

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.

Re: DAX rolling churn for the month (MTD)

v-hashadapu — Mon, 18 Aug 2025 06:31:46 GMT

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.

Re: DAX rolling churn for the month (MTD)

v-hashadapu — Thu, 21 Aug 2025 04:50:11 GMT

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.