Measure for comparing month to month based on two columns

[Helen075]

I am looking for a measure that tracks the change in unique ID numbers, with the ability to break down the changes per area and rating. For example, I have two tables below, one from July and another from June and need to see the differences between the two, similar to the third table below.

I have the below measure that works when I have the Area in a matrix table but stops working once I drill down to the Ratings.

Any help would be much appreciated!

Net Change =

VAR CurrentArea = SELECTEDVALUE('July'[Area])

VAR Added =

    COUNTROWS(

        EXCEPT(

            CALCULATETABLE(

                VALUES('July'[ID]),

                'July'[Area] = CurrentArea

            ),

            CALCULATETABLE(

                VALUES('June'[ID]),

                'June'[Area] = CurrentArea

            )

        )

    )

VAR Removed =

    COUNTROWS(

        EXCEPT(

            CALCULATETABLE(

                VALUES('June'[ID]),

                'June'[Area] = CurrentArea

            ),

            CALCULATETABLE(

                VALUES('July'[ID]),

                'July'[Area] = CurrentArea

            )

        )

    )

RETURN

Added - Removed

June

Area	ID	Rating
A	001	2
B	003	1
B	004	1
C	005	1
C	006	1

July

Area	ID	Rating
A	001	2
A	007	1
B	003	1
C	005	1
C	006	1

End result

Area	Rating	Change
A	1	+1
A	2	0
B	1	-1
B	2	0
C	1	0
C	2	0

[v-pgoloju]

Hi @Helen075,

Just following up to see if the Response provided by community member were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @Helen075,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @Helen075,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @audreygerred  for prompt and helpful response.

Just following up to see if the Response provided by community member were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[audreygerred · Accepted solution]

Hello! Instead of having a separate table for each month, I would have one table and add a date column (just pick a random date in the month). Then you can easily use time intelligence of MoM.

Something like this:

Your output could be like this:

Rating Total TM = SUM('Table'[Rating]) 

Rating Total LM = CALCULATE([Rating Total TM], DATEADD('Table'[Date].[Date],-1,MONTH))

MoM Change = [Rating Total TM]-[Rating Total LM]