Count Customers aggregately based on their latest status change on a monthly basis

[akfir]

Hi guys,
i have this table of customers (Contact Id) with all their status changes along their way on a monthly basis:

i wish to calculate by measures the number of customers for each status for each month, which means the latest status available should be considered and calculated until next status change. once a customer has its first change, he should be calculated until current month.
There is a table to present what i mean (but in columns. not measures):

thanks in advance,
Amit

[magsod · Accepted solution]

OK, now I think I understand your requirement better.

Firstly, this solution requires a separate calendar table!

Secondly, I would suggest to maybe redesign your data model to simplyfy the solution. Maybe calculate the nextChange column in the data table instead.

However, here is a working solution with a measure that internally calculates the nextChange for a contactID and uses that to determine the current status with regards to the calendar table.

Contacts with latest update =

var curDate = MAX('Calendar'[Date])

RETURN

COUNTROWS(

    FILTER(

        CALCULATETABLE(

            ADDCOLUMNS (

                Contacts,

                "nextChange",

                    CALCULATE (

                        MIN ( Contacts[Change Month] ),

                        ALLEXCEPT(Contacts,Contacts[Contact Id]),

                        Contacts[New Status] <> EARLIER ( Contacts[New Status] ),

                        Contacts[Change Month] > EARLIER ( Contacts[Change Month] )

                    )

            ),

            ALL('Calendar')

        ),

        Contacts[Change Month] <= curDate &&

        ([nextChange] > curDate || ISBLANK([nextChange]))

    )

)

[akfir]

**bleep** Perfect!
Thanks buddy.

[magsod]

Hi,
Adjust the code to match your table and column names.

Contacts with latest update =
CALCULATE (
    COUNTROWS ( Contacts ),
    FILTER (
        Contacts,
        Contacts[Change Month]
            = CALCULATE (
                MAX ( 'Contacts'[Change Month] ),
                ALLEXCEPT ( Contacts, Contacts[Contact Id] )
            )
    )
)

Br,

Magnus

[akfir]

Thanks for your response!
it doesnt seem to apply what i need.
for your solution, data is:

The required result should present the below:

Means it should be aggregative - any month a customer joins the data (has its first change)