Forum Discussion
Date Hierarchy issue on a graph
I am self taught on Power BI and loving it! I developed the perfect report for my needs with data from Jan'18 to August '18. Using a field in my data "Posted Invoice Date".
Once I had the report as I wanted it, I exported last years data in (the contract only started in Oct '17). So I now had data from Oct '17 to Aug '18. The data is in date format with Year, QTY, Month, Day hierarchy. When I plot it on a graph at year level it shows 2017 and 2018. However, when I view it at Month level it tags Oct '17 - Dec '17 to the end of the data so the flow is:
Jan '18, Feb '18, Mar '18, Apr '18, May '18, Jun '18, Jul '18, Aug '18, Oct '17, Nov '17, Dec '17
Now I have looked at the X Axis type and when it is at Yearly level the type is "Continuous" and when I go down the heirarchy to Month it's type is "Categorical" and it will not let me change it.
I am sure there is an easy fix - please help if you can.
TIA Hogg
Hi,
This is what you should be doing:
- Create a calendar table with the following calculated column formulas YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
- Create a relationship from the Date column of your base data table to the Date column of your Calendar Table
- Using the "Sort by column" feature, sort the Months in the calendar Table by the Order column in the Calendar Table
- In your visual, drag Year and Month from the Calendar Table
Hope this helps.
3 Replies
Hi,
This is what you should be doing:
- Create a calendar table with the following calculated column formulas YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
- Create a relationship from the Date column of your base data table to the Date column of your Calendar Table
- Using the "Sort by column" feature, sort the Months in the calendar Table by the Order column in the Calendar Table
- In your visual, drag Year and Month from the Calendar Table
Hope this helps.
Ashish, thankyou so much. I knew that I would need to create a Calendar Table for some of my reports but given this one had a date in the correct format I thought I could go without it.
Have put a Calendar Table in and it works a treat.
Cheers
Hoggy
You are welcome. If my reply helped, please mark it as Answer.
