Forum Discussion
Month on Month difference table visual that's sortable
Yes. The limitation you're hitting is that a Power BI Matrix doesn't sort independently by each month column when those months are generated as column groups. The total column can be sorted, but the individual month columns aren't treated as independent sortable fields.
A better approach is to create a flat table visual rather than a Matrix
| Date | Species | Breed | Count |
| ------ | ------- | -------- | ----: |
| 01-Jan | Dog | Labrador | 10 |
| 02-Jan | Dog | Labrador | 15 |
| 01-Feb | Dog | Labrador | 20 |
| 02-Feb | Dog | Labrador | 12 |
First create measures for each month:
January =
CALCULATE(
SUM(Sales[Count]),
MONTH('Date'[Date]) = 1
)
February =
CALCULATE(
SUM(Sales[Count]),
MONTH('Date'[Date]) = 2
)
Then calculate the difference:
MoM Difference =
[February] - [January]
But don't create separate physical columns if you need the solution to work dynamically across years/months.
Better dynamic solution
Use a proper Date table:
DimDate
|
| 1 → *
|
FactTable
Then create:
Current Month =
CALCULATE(
[Total Count],
DATEADD('Date'[Date], 0, MONTH)
)
and:
Previous Month =
CALCULATE(
[Total Count],
DATEADD('Date'[Date], -1, MONTH)
)
Then:
MoM Difference =
[Current Month] - [Previous Month]
For the visual
Use a Table visual:
Species | Breed | Jan | Feb | Mar | Apr | Total
--------------------------------------------------
Dog | Lab | 25 | 32 | 28 | 40 | 125
Dog | Pug | 15 | 19 | 22 | 20 | 76
Cat | Siam. | 30 | 25 | 31 | 35 | 121
However, if you need the user to click Jan, Feb, Mar, etc. and sort the rows by that particular month, a standard Power BI Table/Matrix has limitations when the month columns are dynamic.
Super UserThat behaviour is a known limitation of the matrix visual - when columns are generated from a field (like your months), those column headers cannot be sorted individually. Only the row headers and the grand total column offer sorting.
The usual native workaround is to switch to a Table visual and create one explicit measure per month you want to display, for example:
May = CALCULATE([YourCount], 'Date'[MonthName] = "May")
Jun = CALCULATE([YourCount], 'Date'[MonthName] = "Jun")
In a Table visual every column header sorts independently. The tradeoff is you lose the auto-pivot behaviour a matrix gives you, so if the months change over time you have to maintain the set of measures. For a fixed range this works cleanly.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani