Forum Discussion
Specific date range on axis
After setting up some graphs with the assumptions that our Fiscal Year was July - June I was told that our Fiscal Year started mid June and that the graphs on my report had to show from June of the previous year, to June of the next year.
The data I am displaying is in a single table which has a format like:
| Title | Started | Completed | Cat 1 | Cat 2 |
| Incident 1 | 11/3/2021 | 13/3/2021 | System 1 | High |
| Incident 2 | 3/4/2021 | 5/4/2021 | System 2 | Low |
The graphs I need to create look like:
Changing the option year value needs to update the information for any incidents which started over the selected fiscal year. Any months which have no data still need to appear on the graph, showing no data.
The problem I am running into is getting both the Month / Year axis to update to the proper year when the slicer changes value. Specifically, I am having trouble filtering the data so that is shows both June (Previous year) and June (Current Year).
Does anyone know how to solve this?
Hi Christo,
You need a separate Date Table that also has mapping to Fiscal Year. Check this tutorial to create a date table.
Create date table:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columnsAdd fiscal:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-2-fiscal-columnsThen create a relationship between your table and the date table. And then use Fiscal year column from the date table as a slicer and month-year column from the date table in the visual axis.
3 Replies
Hi Christo,
You need a separate Date Table that also has mapping to Fiscal Year. Check this tutorial to create a date table.
Create date table:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columnsAdd fiscal:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-2-fiscal-columnsThen create a relationship between your table and the date table. And then use Fiscal year column from the date table as a slicer and month-year column from the date table in the visual axis.
Thank you for your help,
After sleeping on this for a bit I was able to figure out why I could not get it working the first time I had tried this solution. The data in my "Started" field contained time data, which means when I first created the relationship between my fiscal calendar table and my data table none of the data matched, so the filters did not work.
I was able to get around this by creating a new column where the time data was set to 12:00.
Thank you for your reply, I will mark it as the solution.
I have been there and done that too lol. Changing the data type to date in Power Query will work too, but it will not work in Power BI desktop, as it will only "hide" the date. Glad that you are able to solve it!