Date Hierarchy Issue

[Trebor]

I have graphs and tables in a report that display a date hierarchy along one axis. When I drill down from year to quarter, it works fine, and when i drill down into a particualar quarter to display that quarters months the drill down works fine.

However when I use the drill down arrow button (top left corner) to drill down to display all months, it gives me one incorrect constant number for all all months. So drill down to all months doesnt work, but drill down to a particular set of months (e.g. just Quarter 1) works fine.

I am developing reports in Power BI desktop and am connected to an analysis services database using DirectQuery.

Any help would be appreciated.

[ccakjcrx · Accepted solution]

@Trebor

Hello!

Given there isn't any printscreens, I have to make some presumptions based on my experience. 

I ran into a similar situation where my months (and weekdays) were sorted in alphabetical order rather than chronoligcal order. In order to solve my problem, I had to add two columns; the first changes the month value to a number (e.g., 1 – 12); the second formats the months name (e.g., Sept).

It is worth noting I had a column in my dataset called Date; it contained the date in mm/dd/yyyy format. Here is the first column (Month B) I added to the model:

Month B = MONTH ( fDispensingHistory[Date].[Date] )

Here is the second column (Month C) I added:

Month C = FORMAT ( fDispensingHistory[Date].[Month], "mmmm" )

In the data view, select Month C column, click the ‘Sort by Column’ at the top (in the modeling tab), and choose Month B to sort by. In your visual, you can then use Month C as the date value, and the months will show up in chronological order.

Doing this allowed me to see months in chronological order when drilling down from quarters to months. 

I got this solution from the PBI community, but I couldn't find the link 😞

Hope this helps!

Found this URL that shows printscreens of problem and solution: 

https://blogs.msdn.microsoft.com/samlester/2017/04/02/sorting-by-month-names-in-power-bi/