Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Community
I'm having some trouble with the date axis on a graph where the show items with no data option defaults to Jan - Dec but the financial year runs May - Apr:
It needs to show 2022 May - 2024 April.
The dates in the calendar table range between 2022-05-01 and 2024-04-30.
What can be done to show future months for the current financial year rather than ending in Dec?
Solved! Go to Solution.
PBI file attached.
Hope this helps.
Hi,
This DAX pattern should work.
FY = if(calendar[month number]>=5,year(calendar[date])&"-"&year(calendar[date])+1,year(calendar[date])-1&"-"&year(calendar[date]))
Month order = related(Monthorder[Order])
Thank you for your suggestion Ashish_Mathur - unfortunately it didn't work.
I have shared a link to the pbix (in my reply to Wilson_ below) with your suggested solution in it.
Hi, thanks for sharing the pbi file.
You have a date hirerachy that is reading year, month, but there are no columns for those. If you create a column for example for MonthYear :
Personal preference:
Your calendar table has too much information. If things are not required on your model, remove the columns in power query and keep it simple. You are missing basic columns for month, year etc. Using a date only column doesnt work. You can also change the starting date in power query, to filter older dates, if you know there will never be any data there.
Thanks for the suggestion NikolF, however I'd like the visual to show previous financial year and all 12 months of the current financial year, so it would show March and April 2024 on the axis.
You're right, there is a lot going on in the calendar table but it is being used else where in the report.
The months will appear as new data comes in - in case this is not clear. Because there is null data for the rest of the months, there is nothing to show. In my opinion this is a good clean visual of the fiscal year.
If you want more months to show regardless, create a FY date table, connect it to your data, like a previous suggestion by using FY date column to connect to the date of your data and use that table in full, by filtering the FY years you want, to see the rest of the months of that table.
C4L84,
Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂
Hi Wilson_
Here's the link to the pbix: https://www.dropbox.com/scl/fi/gvza5bub4k09e0qi3q57o/Date-x-axis-show-items-with-no-data-defaulting-...
You are welcome.
User | Count |
---|---|
98 | |
91 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |