topic Re: Monthly Count Of Customers Status by their status at the end of each month in DAX Commands and Tips

Monthly Count Of Customers Status by their status at the end of each month

akfir — Thu, 03 Nov 2022 15:09:49 GMT

Hi,
i wish to summarize the number of customers with their status (New Value) as for end of each month, as shown in my dummy tables.
Each Month in the result table actually presents the number of customer by the END OF THE MONTH for each status.
please note that the number of customers might increase from month to month as new customers join the database on a daily basis.
since a customer joins the database, it should be considered and being calculated until current month with his latest New Value (current status)

Re: Monthly Count Of Customers Status by their status at the end of each month

amitchandak — Fri, 04 Nov 2022 02:46:06 GMT

akfir , Try if this can help

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

Re: Monthly Count Of Customers Status by their status at the end of each month

Anonymous — Fri, 04 Nov 2022 03:01:03 GMT

HI akfir,

You can try to use the following dax formula to create a new calculated table to summary records based on last datae status:

summary = GROUPBY ( FILTER ( ADDCOLUMNS ( 'Table', "flag", IF ( [Change Date] = MAXX ( FILTER ( 'Table', [Customer ID] = EARLIER ( 'Table'[Customer ID] ) && YEAR ( [Change Date] ) = YEAR ( EARLIER ( 'Table'[Change Date] ) ) && MONTH ( [Change Date] ) = MONTH ( EARLIER ( 'Table'[Change Date] ) ) ), [Change Date] ), 1, 0 ), "MonthYear", FORMAT ( [Change Date], "mmm-yy" ), "Actived", IF ( [New Value] = "Active", 1, 0 ), "Inactive", IF ( [New Value] = "Inactive", 1, 0 ) ), [flag] = 1 ), [MonthYear], "#Active", SUMX ( CURRENTGROUP (), [Actived] ), "#Inactive", SUMX ( CURRENTGROUP (), [Inactive] ) )

Regards,

Xiaoxin Sheng

Re: Monthly Count Of Customers Status by their status at the end of each month

akfir — Fri, 04 Nov 2022 06:27:49 GMT

thanks for your reply!
for the below dummy data:

by your suggestion i get the below result:

it is wrong as in this case it is supposed to show:

please not that although there is no any status change in May'22 at all, i still wish to present May'22 statuses - according to the latest change available.

Amit

Re: Monthly Count Of Customers Status by their status at the end of each month

akfir — Fri, 04 Nov 2022 06:50:51 GMT

Thanks for your reply!
i was following your solution on the video you shared.
it is a little different from what i need, as i need to show the last status of a customer by the end of each month of the year. my main goal is aggregately counting the active and inactive customers for EACH month (even if there is no status change in a specific month, then i wish to count the latest status of the last month).
hope it is clear.

Re: Monthly Count Of Customers Status by their status at the end of each month

akfir — Sun, 06 Nov 2022 14:31:30 GMT

Hi any help please?

Re: Monthly Count Of Customers Status by their status at the end of each month

akfir — Sun, 06 Nov 2022 14:31:42 GMT

Hi any help please?

Re: Monthly Count Of Customers Status by their status at the end of each month

Anonymous — Fri, 18 Nov 2022 07:23:56 GMT

Hi akfir,

So, you mean this status should also be also calculated unital to the end even if this status not changed? If that is the case, you can modify the formula to change calculate records from accurate month to rolling multiple month records:

summary = GROUPBY ( FILTER ( ADDCOLUMNS ( 'Table', "flag", IF ( [Change Date] = MAXX ( FILTER ( 'Table', [Customer ID] = EARLIER ( 'Table'[Customer ID] ) && YEAR ( [Change Date] ) = YEAR ( EARLIER ( 'Table'[Change Date] ) ) && MONTH ( [Change Date] ) <= MONTH ( EARLIER ( 'Table'[Change Date] ) ) ), [Change Date] ), 1, 0 ), "MonthYear", FORMAT ( [Change Date], "mmm-yy" ), "Actived", IF ( [New Value] = "Active", 1, 0 ), "Inactive", IF ( [New Value] = "Inactive", 1, 0 ) ), [flag] = 1 ), [MonthYear], "#Active", SUMX ( CURRENTGROUP (), [Actived] ), "#Inactive", SUMX ( CURRENTGROUP (), [Inactive] ) )

Regards,

Xiaoxin Sheng

Re: Monthly Count Of Customers Status by their status at the end of each month

aanyoti — Fri, 04 Aug 2023 08:43:03 GMT

Hi akfir ,

I have the exact same requirement, did you find a solution? Please advise.