Count the Month over Month change in Category

[bchager]

Hi everyone,

I need to write an expression to count the month over month change in category, per company. For example, the MoM change from 12/1/2024 - 1/1/2025 for the New Category should be 2.

3 companies (numbers 6, 7, and 4) have the new Category on 1/1/25, but since company 4 also had the New category on 12/1/2024 (or in any previous month), it shouldn't be counted.

Kudos to anyone who can help!

Company	Category	Date
1	Discovery	12/1/2024
2	Connected	12/1/2024
3	New	12/1/2024
4	New	12/1/2024
5	Connected	12/1/2024
1	Committed	1/1/2025
2	Discovery	1/1/2025
8	Connected	1/1/2025
3	Discovery	1/1/2025
6	New	1/1/2025
7	New	1/1/2025
4	New	1/1/2025
5	Connected	1/1/2025

[bchager · Accepted solution]

MoM Change =

VAR CurrentMonth =

    MAX('Table'[Date])

VAR CurrentCategory =

    MAX('Table'[Category])

VAR PreviousMonths =

    CALCULATETABLE(

        VALUES('Table'[Company]),

        FILTER(

            ALL('Table'),

            'Table'[Date] < CurrentMonth

            && 'Table'[Category] = CurrentCategory

        )

    )

VAR CurrentMonthCategoryCompany =

    CALCULATETABLE(

        VALUES('Table'[Company]),

        FILTER(

            'Table',

            'Table'[Date] = CurrentMonth

            && 'Table'[Category] = CurrentCategory

        )

    )

RETURN

    COALESCE(

        COUNTROWS(

            EXCEPT(

                CurrentMonthCategoryCompany,

                PreviousMonths

            )

        ),

        0

    )

[BIswajit_Das]

Hello @bchager 
Just to be confirmed I'm sharing some SS below
Please confirm if this matched with your requirements;

Thanks & Regards

[bchager]

@BIswajit_Das Thank you for taking a look. Yes those numbers are correct.