Sorting graph based on fiscal month.

[ajdm2007]

Hey everyone,

I’ve got this calendar table that I built with DAX. I’ve been tweaking it as I need more time-based features.

As you can see, the table has the basic fields, but I’ve also added the month name, day of the week, the first day of the month (for calculating metrics based on the previous month), and the last column is for the fiscal month since our fiscal year at work starts in July and ends in June.

I’m sharing the formula in case anyone else wants to use it.

calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,01,01),DATE(2030,12,31)),
    "Year", YEAR([DATE]),
    "Month", MONTH([DATE]),
    "Quarter", FORMAT([DATE], "\QQ"),
    "MonthName", FORMAT([DATE], "MMM"),
    "WeekDay", FORMAT([DATE], "dddd"),
    "Day", DAY([DATE]),
    "FirstMonthDay", FORMAT(EOMONTH([DATE], -1) + 1,"mm/dd/yyyy"),
	"FiscalMonth", IF(MONTH([DATE]) >= 7, MONTH([DATE]) - 6, MONTH([DATE]) + 6)
	)

In the chart below, I have two metrics: the total number of tickets submitted and the total number of tickets resolved. These are plotted on the X-axis, and I’ve placed the months of the year on the Y-axis. However, for some reason, the months aren’t ordered correctly, meaning they’re not in fiscal month order.

Any ideas on how to fix this?

Thank you so much in advance, 

[Shravan133 · Accepted solution]

Hi @ajdm2007 ,

You need to select your MonthName column and then sort by the desired column(Fiscal Month). Then use MonthName in your visauls. Look at my screenshot in previous reply.

In your screenshot, you have selected fiscal month column and sorted by fiscal month column, which is wrong.

[Shravan133]

Sort your month coulmn in teh date table by a  month number column.

In your case, select MonthName, ColumnTools--> Sort By Column--> Month.

Then use the MonthName in your visuals and they should sort accordingly.

[ajdm2007]

Hello @Shravan133 

In my case is FiscalMonth, so I followed your instruccions and the graph remined unchanged.

Actually, when I add the FiscalMonth field to the graph this is what happening.

For some reason, the sorting is no working.

Any idea?

Thank you in advance,