Forum Discussion
Date Hierarchy Issue
Anonymous
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/
Anonymous
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/