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

[akfir]

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)

[Anonymous]

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

[akfir]

Hi any help please?

[akfir]

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

[Anonymous · Accepted solution]

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

[amitchandak]

@akfir , Try if this can help

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

[akfir]

Hi any help please?

[akfir]

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.

[aanyoti]

Hi @akfir ,

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