Date x axis show items with no data defaulting to Jan - Dec

[C4L84]

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?

[Ashish_Mathur]

Hi,

This DAX pattern should work.

1. Create a Calendar Table with calculated column formulas for Month name and Month number
2. Write this calculated column formula to determine the FY

FY = if(calendar[month number]>=5,year(calendar[date])&"-"&year(calendar[date])+1,year(calendar[date])-1&"-"&year(calendar[date]))

1. Create a 2 column table - Month Name and Order.  In the order column, assign numbers to each month with 1 being May and 12 being April
2. Create a relationship (Many to One and Single) from the Month name column of the Calendar Table to the Month name column of the table created in the step above
3. In the Calendar Table, write this calculated column formula

Month order = related(Monthorder[Order])

1. In the Calnedar Table, sort the Month name column by the Month order column
2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.
3. To your visual, drag Year and Month name from the Calendar Table.

[C4L84]

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.

[NikolF]

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 :

Month Year = FORMAT('Calendar'[Date], "MMM YY") // your axis

MonthYear_sort = FORMAT('Calendar'[Date], "YYYYMM") //sort previous column, and sort order by month

Legend: Use FY measure by filtering for the fiscal years you want (or just use this measure as a visual/page filter to remove other information, like previous dates etc)

This is the result

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. 

[C4L84]

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.

[NikolF]

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. 

[Wilson_]

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. 🙂

[C4L84]

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-...

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[C4L84]

Thank you @Ashish_Mathur! 👍

[Ashish_Mathur]

You are welcome.