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
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,
Solved! Go to 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.
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.
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.
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,
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
102 | |
92 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |