Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
)
Solved! Go to 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 , 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
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?
Did you ever find a solution to your question?
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 😞
no, but would like to.