Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

High level theory on Date Dimensions

Hi, I have a general question on date dimensions. I notice in the Power BI demos, the "automagic" hidden date table that gets created, and many examples where people create their own date tables, have data that looks something like this:

 

Date Year Month

1/1/2000   2000   January

1/1/2001   2000   January

1/1/2002   2000   January

...

12/31/2000   2000   December

 

My general question is why are the months seemingly always defined as just a month name, vs. something like "January 2000", "December 2000" with month name plus the year?

 

This causes a ton of issues, like:

1. When drilling, many times the graph drills to the "month" level, which shows only the month name. So now your visualization shows the total of all Januarys ever, then the total of all Februarys ever, etc. There may be very fringe cases where I'd want to do this...but those are at best a VERY small percentage. Instead what I'd expect to see is "Jan 2003", "Feb 2003", "Mar 2003", etc.

 

2. If users don't want drill features, now they have to always drag around two columns instead of just one.

 

3. It seemingly breaks the concept that in a hierarchy, each child belongs to one and only one parent. "January 2003" rolls up to the year 2003. What parent does "January" roll up to? Answer - each and every year???

 

4. It makes doing rolling trend reports more difficult. If I want to pick the last 6 months when the month has both month and year, I pick "Sep 2017", "Oct 2017", "Nov 2017", "Dec 2017", "Jan 2018", and "Feb 2018". If I want to do this when I only have individual month and year columns, I pick Year = 2017 and 2018, and month = "Sep" thru "Dec" plus "Jan" and "Feb". But now values I don't want like "Feb 2017" show up.

 

Yes, I know there are time intelligence functions that maybe help with some of this...but for a beginner, the whole idea of having a month without a year is pretty silly. "Show me the numbers for October!" "ummm....what year???"

 

Is there something basic I'm missing? Is there a reason why most of the beginner videos don't define months (and quarters, and weeks for that matter) in what seems to be a very normal way? Trying to understand if there is something magic about defining the date dimensions in Power BI this way.

 

Thanks!

Scott

 

 

No Replies