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

[talitanieps]

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!

[amitchandak]

@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:
https://www.youtube.com/watch?v=KK1zu4MBb-c

[talitanieps]

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!