Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ajdm2007
Helper III
Helper III

Sorting graph based on fiscal month.

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.

 

ajdm2007_0-1725474267134.png

 

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.

ajdm2007_1-1725475122027.png

 

Any ideas on how to fix this?

 

Thank you so much in advance, 

1 ACCEPTED SOLUTION
Shravan133
Solution Sage
Solution Sage

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.

 

View solution in original post

3 REPLIES 3
Shravan133
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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

Shravan133_0-1725485491562.png

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

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

Hello @Shravan133 

 

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

ajdm2007_0-1725547982478.png

 

ajdm2007_1-1725548046787.png

 

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

 

 

ajdm2007_2-1725548221331.png

 

 

For some reason, the sorting is no working.

 

Any idea?

 

Thank you in advance, 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.