cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trebor
Advocate I
Advocate I

Date Hierarchy Issue

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.

1 ACCEPTED SOLUTION
ccakjcrx
Resolver I
Resolver I

@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/

View solution in original post

1 REPLY 1
ccakjcrx
Resolver I
Resolver I

@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/

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors