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.

OffColour1972​ 

could you pls provide some sample data and expected output?

Hi OffColour1972​,

A matrix isn't really suited to this if you need every displayed column to be independently sortable. The column headers in your example are part of the matrix structure, so Power BI doesn't treat them like normal table columns for sorting.

If the months are known/fixed, I'd use a Table visual instead and create separate measures for each value you want to display.

For example:

March Total =
CALCULATE(
SUM(Sales[Amount]),
'Date'[Month] = "March"
)

April Total =
CALCULATE(
SUM(Sales[Amount]),
'Date'[Month] = "April"
)

March-April Diff =
[April Total] - [March Total]

Then put Species plus those measures into a Table visual:

Species | March Total | April Total | March-April Diff | May Total | ...

With a Table visual, the user can click the individual column headers and sort by that particular measure.

If the months need to be completely dynamic, that's where it gets more complicated. A matrix is better for dynamically generating the month columns, but you lose some of the straightforward column-by-column sorting you're looking for.

So for a fixed number of months, I'd go with a Table + measures. If the months need to change dynamically based on the data/selection, I'd look at a different design using calculation groups or field parameters.

That 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