cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## How to order months in a Calendar Table differently (in DAX or M)?

Hello PBI Community!

I have been researching this for few days now, and I can't seem to do it, it's driving me nuts! Hope you're able to help me with a solution for my case.... Here we go! 😄

So, I'm working in a report to display the performance of ad campaigns, through time; our idea is to compare same months of the campaigns, for instance: april 2021 with april 2022, and in order to do that I need to sort months differently than by the number of the month.

so, for example, instead of showing in the graph the numbers from Jan, Feb, Mar, Apr, i'd like the graph to start in April:

 campaign start end month_No month order 2021 01/11/2021 30/05/2021 11 1 12 2 1 3 2 4 3 5 4 6 5 7 2022 01/05/2022 30/11/2022 5 1 6 2 7 3 8 4 9 5 10 6 11 7

In this graph, which is as-is today, we can see numbers from jan to dec, but since the campaign started actually in november (in orange), the correct order would be nov, dec, jan, feb.....
orange = 2021
blue = 2022

I tried creating a table in excel, with the order previously set for each campaign, but it didn't work; I'd like to come up with a DAX-Column code that understands that and returns the month order as per explained.. The thing that it's making it more difficult (at least, in my eye) is the need to reference this campaign, so there's this conditional factor, for the campaigns are different among products, and have different start months and end months, each year.... so I can't just hard code the month order for each campaign, the ideal would be to come up with a code, either in M or DAX...

How could I possibly achieve that in PBI?
Thanks a lot for your assistance!

2 REPLIES 2
Super User

@talitanieps , You need have two column like

Month = format([Date], "mmm")

Month Sort = if(month([Date]) <4, month([Date]) +9 , month([Date]) -3)

then mark month Sort as Sort column on Month and use month in visual

How to Create Sort Column and Solve Related Errors:

Hello Amit,

Thanks a lot for your comment - I ended up using rankx, which gave me the same results as your proposed solution, you comment guided me to the right path, thanks a lot!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors