Date Hierarchy issue on a graph

[Hoggy]

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

[Ashish_Mathur · Accepted solution]

Hi,

This is what you should be doing:

1. Create a calendar table with the following calculated column formulas YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
2. Create a relationship from the Date column of your base data table to the Date column of your Calendar Table
3. Using the "Sort by column" feature, sort the Months in the calendar Table by the Order column in the Calendar Table
4. In your visual, drag Year and Month from the Calendar Table

Hope this helps.

[Hoggy]

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

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.