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_Order123456789101112Month_Num234567891011121 fiscal year July-JuneMonth_FY_Order789101112123456Month_Num123456789101112 production year April-MarchMonth_PY_Order101112123456789Month_Num123456789101112 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 )

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

Frequent Visitor

3 years ago

Solved

dynamic custom sort on month

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
)