Problems sorting correctly by date

[mac_xpert]

Hi.

I have a Stacked Area Chart that shows financial data starting in May 2017 and running through to April 2018.

I have a column of correctly formatted dates in my query. I've set the data type for the data to Date. The problem is when viewing by month, i get months displayed jan - - december, despite the fact that they are spread over two years, so should display May - April.

So i get a graph like this:

When it should look like this:

This i achieved by creating a plain text field with the dates in the format '2017-MAY', '2017-JUN', '2017-JUL' ....... '2018-MAR', '2018-APR' i then set the Modelling>'sort by column' value to my date column.

The problem with this is because the plain text field is just that - i dont get the ability to drill down through year / quarter /month etc.

Should Power Bi be able to give me a properly sorted date range over serveral years? Any help would be appreciated as i'm not sure if i'm doing something wrong, or there is a 'bug' in Power Bi that makes this impossible.

Thanks all in advance! 

[parry2k · Accepted solution]

You have to create your own hierarchary for drill, add Year/<your month field>/Date as part of hierarchary. if need further help , let me know.

[v-sihou-msft · Accepted solution]

@mac_xpert

As @parry2k said, you need to create a Calendar hierarchy, and drill down to Month level to get your expected result.

Regards,

[parry2k · Accepted solution]

You have to create your own hierarchary for drill, add Year/<your month field>/Date as part of hierarchary. if need further help , let me know.