How to Ensure Slicer and Graphs are Sorted by Year and Month

[schilders]

I have a business object defined as follows:

DateFrom = 
ADDCOLUMNS (
CALENDAR(MINX('Auth Data','Auth Data'[Admit Date]),DATE(YEAR(MAXX('Auth Data','Auth Data'[Admit Date])),12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

I use a slicer that includes Year, Quarter and Month.  How do I ensure all sort levels are correct?  When I sort by Month Number (a whole number), the slicer does not correctly sort as intended.  I want to sort by MonthNumber but only need the slicer to dispaly Year, Quarter and Month.

Additionally, the graphs in the dashboard that use the Year and Month do not render in chronological order.  How do I ensure that the values are plotted on the x-axis in year and month order?

[schilders]

Thanks so much for your help.  After applying those sorts, my graph still does not render in precise chronological order.  I'm using the 12 months ending October 31, 2022 but the last x-axis value is December.  Can you suggest a workaround for this?

[danextian]

Hi @schilders ,

FORMAT function results  to a text  string so whatever the results are, they're sorted alphabetically. You can use use sort by another column in Power BI to do a custom sorting. Month as text is to be sorted by month number - MONTH(Dates[Date]) and Quarter to be sorted by the quarter number - ROUNDUP(MONTH([Date])/3, 0)

[schilders]

@danextian  Thanks for your response.  My modified DateFrom is as follows:

DateFrom = 
ADDCOLUMNS (
CALENDAR(MINX('Auth Data','Auth Data'[Admit Date]),DATE(YEAR(MAXX('Auth Data','Auth Data'[Admit Date])),12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", Month([Date]),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & ROUNDUP( MONTH([Date])/3, 0), "Quarternumber", ROUNDUP( Month([Date])/3, 0 ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

I've applied the sorts on the object as suggested and my slicer now works as intended.  What do you suggest in terms of addressing the chronological order of months on the x-axis of my graphs?  Snippet below after changes to DateFrom object:

[danextian]

Hi @schilders ,

Sort these two columns by Monthnumber