Forum Discussion
Delta between current month and last month (Monthly Data)
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?
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 |
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 __ResultBut, 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
2 Replies
- Greg_Deckler
Community Champion
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 __ResultBut, 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
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?
