Delta between current month and last month (Monthly Data)

[ksha_001]

I have a monthly data table with details of multiple clients and their entity IDs. I am trying to calculate the difference between the current month's entity count and the previous month's entity count. I attempted to use some formulas but did not get the desired output. I created a calendar for the dates and then calculated the current and previous month's data. Can someone please assist me with this?

Current Month = CALCULATE(COUNT('Query1'[AH_Internal_Claim_ID]), FILTER(ALL('DT'),'DT'[Month Rank]=max('DT'[Month Rank])))

Last Month = CALCULATE(COUNT('Query1'[AH_Internal_Claim_ID]), FILTER(ALL('DT'),'DT'[Month Rank]=max('DT'[Month Rank])-1))

Please see the below example for reference:

Input data

transaction	Months	company
9	20230331	XYZ
10	20230331	XYZ
11	20230331	XYZ
12	20230331	XYZ
13	20230331	XYZ
14	20230331	XYZ
1	20230331	ABC
2	20230331	ABC
3	20230331	ABC
4	20230331	ABC
5	20230331	ABC
113	20230331	TMT
114	20230331	TMT
115	20230331	TMT
116	20230331	TMT
15	20230430	XYZ
16	20230430	XYZ
17	20230430	XYZ
18	20230430	XYZ
19	20230430	XYZ
20	20230430	XYZ
6	20230430	ABC
7	20230430	ABC
8	20230430	ABC
9	20230430	ABC
10	20230430	ABC
117	20230430	TMT
118	20230430	TMT
119	20230430	TMT
120	20230430	TMT
21	20230531	XYZ
22	20230531	XYZ
23	20230531	XYZ
24	20230531	XYZ
25	20230531	XYZ
26	20230531	XYZ
11	20230531	ABC
12	20230531	ABC
13	20230531	ABC
14	20230531	ABC
15	20230531	ABC
16	20230531	ABC
17	20230531	ABC
18	20230531	ABC
19	20230531	ABC
121	20230531	TMT
122	20230531	TMT
123	20230531	TMT
124	20230531	TMT
125	20230531	TMT
126	20230531	TMT
127	20230531	TMT
128	20230531	TMT
129	20230531	TMT
130	20230531	TMT

Output: 

Company	20230331	20230430	20230531	Current-Previous Month
ABC	5	5	9	4
TMT	4	4	10	6
XYZ	6	6	6	0

[Greg_Deckler · Accepted solution]

@ksha_001 Should be something like this:

Measure = 
    VAR __Current = MAX('Table'[Months])
    VAR __PrevMonth = MAXX(FILTER('Table', [Months] <> __Current), [Months])
    VAR __CurrentTable = FILTER('Table', [Months] = __Current)
    VAR __PrevMonthTable = FILTER('Table', [Months] = __PrevMonth)
    VAR __Result = COUNTROWS(__CurrentTable) - COUNTROWS(__PrevMonthTable)
RETURN
    __Result

But, to get it in the form that you are showing, you will need something like this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

[ksha_001]

Thank you @Greg_Deckler . That worked. Regarding renaming the total field, I have a question. When I try to rename the field by right-clicking, the change doesn't reflect on the canvas. Is this a Power BI limitation?