cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Trebor Frequent Visitor
Frequent Visitor

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

Accepted Solutions
ccakjcrx Member
Member

Re: Date Hierarchy Issue

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

Re: Date Hierarchy Issue

@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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,758)