Trailing 12 months, year over year, sort by descending values

[Laudeq01]

In the image you can see my monthly power output from solar panels, year over year. I would like to sort data on two levels.

1. Month (Jan, Feb, March, ...) as it is currently displayed.

2. For each month, productions displayed in descending order. For instance for February, first bar would be 2025 (308kw) and second bar would be 2024 (183kwh). By defaut it is displayed in ascending year order.

I have a Date table

[v-dineshya · Accepted solution]

Hi @Laudeq01 ,

Thank you for reaching out to the Microsoft Community Forum.

Can you please follow below steps:

1. Ensure your Date Table is set up

Date table : Date, Month, MonthNumber and Year

MonthNumber = MONTH([Date])

Month = FORMAT([Date], "mmmm")

Year = YEAR([Date])

Then in your Month column, sort by MonthNumber to maintain Jan → Dec order.

2. Create a Ranking Measure for Each Month
You need to dynamically rank the years within each month by descending kWh. Let’s assume your fact table is called Production and has: Date, kWh and related to your Date table.

Create measure:

RankByMonth =
RANKX(
FILTER(
ALLSELECTED('Production'),
MONTH('Production'[Date]) = MONTH(SELECTEDVALUE('Production'[Date]))
),
CALCULATE(SUM('Production'[kWh])),
,
DESC,
DENSE
)

Note: Production[Date] depending on your actual field name.

3. Use a Matrix or Clustered Column Chart
If you're using a clustered column chart with:

Axis: Month (from your Date table), Legend: Year and Values: kWh

Then you’ll need to break the legend and recompose it with a calculated column or field that includes the year and rank.

4. Create a Composite Column for Sorting
Add this calculated column in your fact table:

SortKey =
'Production'[MonthNumber] * 100 + [RankByMonth]

Then, use this column in a custom visual or to sort a composite axis.

Note:

If DAX ranking is messy in a chart, consider:

Switch to a matrix visual. Put Month on Rows. Put Year on Columns. Show kWh as Values. Sort the values descending for each row. This keeps months in order and lets you see descending values per month at a glance.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[HarishKM]

@Laudeq01 Hey,
I am using this in one of my report.

Try above and it will solve your requirements.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[HarishKM]

@Laudeq01 Hey,
You can try this method

Step 1: Create a new column 

"Month", MONTH([Date])

Step 2: you need sort your month year column by newly created calculated column.

step 3: sort your chart month year - engineer by ASC order.

this will work as charm for your requirement.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Laudeq01]

@HarishKM Hi,

This I can do also but it does not fulfill my requirements.

What I want is to sort by month number first, then by decreasing production.

It means I want to see the two bars for January as the first bars of my chart : Jan 24 and 25 (the biggest first)

Then the two bars of February (Feb 24 and Feb 25) , starting by the biggest.

Etc.

Do you see what I mean ?

[Laudeq01]

Thanks grazitti_sapna.

I tried to create your measure "SortOrder" but I get an error message for the part:

FILTER(
'YourTable',
'YourTable'[Month] = EARLIER('YourTable'[Month])

The error message is : EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

The argument above is underlined in red in PBI Desktop.

[grazitti_sapna]

Hi @Laudeq01,

By default multi level sort is not supported in Power BI but we can achieve it, try using below DAX:-

SortOrder =
RANKX(
FILTER(
'YourTable',
'YourTable'[Month] = EARLIER('YourTable'[Month])
),
'YourTable'[PowerOutput],
,
DESC,
DENSE
)

Replace 'YourTable' with your actual table name

Replace [PowerOutput] with your actual solar output column

Replace [Month] with your month column (abbreviated names like Jan, Feb)

Use Composite Axis (Month + SortOrder)

In your visual (e.g., clustered column chart):

• X-Axis: Use Month (to group), but also add Year

• Then go to the "Sort by column" feature (under "Column tools") and:

  • Sort the Year column by the SortOrder column you just created

Fix Month Sorting (If Needed)

If months are out of order alphabetically (Apr before Feb), make sure you have a MonthNumber column (1 for Jan, 2 for Feb...), and Sort 'Month' column by 'MonthNumber'

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!