Forum Discussion
Helper IIIWhy is PBI sorting datetime drilldown by calendar rather than chronological order?
I have data from the end of last till today. When I drilldown from anything below "by Year", PBI always sorts from beginning of the year to the end of the year? It's hard to explain so allow me to demonstrate.
- "by Year":
- What I get: 2015, 2016 (correct)
- "by Quarter":
- What I get: Qtr 1, Qtr 3, Qtr 4
- What I should get: Qtr 3, Qtr 4, Qtr 1
- "by Month":
- What I get: January, February, September, October, November, December
- What I should get: September, October, November, December, January, February
Why is it this way? This is so obviously not what anyone would want (especially not a layman like myself).
Using month below as an example. Identical logic applies across the entire date spectrum.
The Month field is sorted by a MonthNumber field. Simple as that. Power BI only knows it has a month name and what order those months appear in a year. It doesn't perform any analysis to say "Hey this guy's data runs from September 2015 through February 2016." How should it handle the general case where there might be many Septembers from many years in a data set? Dates aren't special. They are just category labels. September is an attribute that exists in the date dimension that is related to a subset of fact rows. When it's a label, all Septembers are grouped together for display.
The best practice that we follow with all of our customers is to have the following fields:
- [MonthNumber] = integer 1-12; hidden, sorts [MonthName] and [MonthNameShort]
- [MonthIndex] = 1-based index of months since the start of the calendar; doesn't wrap at year boundaries; hidden, sorts [Month] very useful for "last month" or "rolling 3 months" - avoids edge cases at year boundaries
- [Month] = YYYY MMM; primary display field
- [MonthName] = MMMM; alternate display field, not typically utilized often
- [MonthNameShort] = MMM; alternate display field, not typically utilized
With these, you cover most of your bases with dates.
4 Replies
- Phil_Seamark
Microsoft Employee
What is your datasource?
- greggyb
Resident Rockstar
Using month below as an example. Identical logic applies across the entire date spectrum.
The Month field is sorted by a MonthNumber field. Simple as that. Power BI only knows it has a month name and what order those months appear in a year. It doesn't perform any analysis to say "Hey this guy's data runs from September 2015 through February 2016." How should it handle the general case where there might be many Septembers from many years in a data set? Dates aren't special. They are just category labels. September is an attribute that exists in the date dimension that is related to a subset of fact rows. When it's a label, all Septembers are grouped together for display.
The best practice that we follow with all of our customers is to have the following fields:
- [MonthNumber] = integer 1-12; hidden, sorts [MonthName] and [MonthNameShort]
- [MonthIndex] = 1-based index of months since the start of the calendar; doesn't wrap at year boundaries; hidden, sorts [Month] very useful for "last month" or "rolling 3 months" - avoids edge cases at year boundaries
- [Month] = YYYY MMM; primary display field
- [MonthName] = MMMM; alternate display field, not typically utilized often
- [MonthNameShort] = MMM; alternate display field, not typically utilized
With these, you cover most of your bases with dates.
cparker4486Hi greggyb,
Thanks for the information. I get what you're saying when you said, "When it's a label, all Septembers are grouped together for display." If I had had multiple Septembers I probably would have come to this realization.
The problem is that this is just so unexpected for someone with little to no experience.
Thanks for the advice on what I can do to correct things. Microsoft should automatically add whatever columns are needed when a datetime is present (or give the user the option to do so.) I don't know... I don't recall Tableau behaving this way (but I haven't used it in a long time.)
