Why is PBI sorting datetime drilldown by calendar rather than chronological order?

[cparker4486]

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).

[Phil_Seamark]

What is your datasource?

[greggyb · Accepted solution]

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.

[cparker4486]

Hi 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.)

[greggyb]

Our starter date table in my company is dozens of columns wide. And it truly is just a starter, a base from which we work depending on the needs of the customer. If Microsoft included every field that might be useful in data analysis, there'd be more than 100 columns easily. And even then, it'd only be valid for a single standard calendar.