dynamic custom sort on month

[vgt56gyr2]

Hi,

Problem: trying to get the x-axis column chart to dynamically sort on user selection based on date slicer --> mat, fiscal year, production year, budget... there are other periods...

As of Feb, we have moving annual total (mat) months order --> rolling window Feb-Jan, next month will be Mar-Feb etc...

Month_MAT_Order	1	2	3	4	5	6	7	8	9	10	11	12
Month_Num	2	3	4	5	6	7	8	9	10	11	12	1

fiscal year July-June

Month_FY_Order	7	8	9	10	11	12	1	2	3	4	5	6
Month_Num	1	2	3	4	5	6	7	8	9	10	11	12

production year April-March

Month_PY_Order	10	11	12	1	2	3	4	5	6	7	8	9
Month_Num	1	2	3	4	5	6	7	8	9	10	11	12

cannot use sort by column (or can we call it by code?) using this code as it doesnt change the salesdata dynamically by filter/slicer, how can the axis be sorted in the correct order by user selection? Looks like it cant be done?

Month_Order =
var DateSlicer = SELECTEDVALUE(Date_Slicer_Compare_Periods[Current])

return
switch(true(),
DateSlicer="12 Months (MAT)",item_sales[MAT_Month_Order],
DateSlicer="6 Months (MAT)",item_sales[MAT_Month_Order],
DateSlicer="3 Months (MAT)",item_sales[MAT_Month_Order],
DateSlicer="Fiscal Year to date",item_sales[FY_Month_Order],
1
)

[amitchandak]

@vgt56gyr2 , You can create a new sort column

if(month([Date]) <=6, month([Date]) +6, month([Date]) -6)

and you can mark it as sort column for MMM or MM

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

[vgt56gyr2]

Hi, it doesn't really answer my question.

I already have a sort order column for these custom periods.

how can the axis be sorted in the correct order by user selection? 

[vnl3]

Did you ever find a solution to your question?

[vgt56gyr2 · Accepted solution]

use the rankx measure for each sort order column:

How to sort by multiple columns in Power BI [Table Visual Examples] - SPGuides

but unfortunately we still cant hide columns in the chart! lol, have to use formatting 😞

[vgt56gyr2]

no, but would like to.