cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akfir
Helper IV
Helper IV

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

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

akfir_0-1667798271805.png

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

akfir_1-1667798586538.png
thanks in advance,
Amit

 

1 ACCEPTED SOLUTION
magsod
Solution Supplier
Solution Supplier

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

 

View solution in original post

4 REPLIES 4
magsod
Solution Supplier
Solution Supplier

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

 

**bleep** Perfect!
Thanks buddy.

magsod
Solution Supplier
Solution Supplier

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

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

akfir_1-1667809365769.png

 

The required result should present the below:

akfir_2-1667809395331.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors