All great achievements require time.
– Maya Angelou
Trying to round out my posts of Fundamentals here – so when I ask the crazy kids on the forum “Are you building this against a Calendar table?” – I have somewhere to point them for reference. If you are seasoned… probably not a ton of excitement for you here 
Power Pivot has a lot of really cool Time Intelligence functions… none of which have much to do with Time 🙂 They all have to do with Dates.
If you look at any of the functions in the list (say DATESYTD), the functions take some weird parameter about <dates>. And the documentation is just horrible. So… what’s up? What is that thing?
The parameter is simply the date column out of your Date table (or “Calendar” table if you prefer, I still can’t decide).
What is a Date Table? Well, it’s… you know… a table… of dates 🙂
The Date Table
Seriously, it is just a lookup table that has a column of dates – and the only caveat is that it needs to have EVERY date in the range of dates you “care about” (say, first sale date until today). Specifically, you may not leave gaps like… weekends and holidays… out of your date table. Bad things will happen.
Once you have that, you can also Mark your table as the Date Table, but I am not convinced that actually does anything to your measures. I always forget to do it, and bad things almost never happen to me. Well, at least not with respect to date tables. You can see the docs here though — as it does give you some nicer filtering options in your pivots.
And… that is really all that is required to have a Date table.
Are They Awesome?
Realize that even if you didn’t have these fancy time intelligence functions you would probably STILL want to have a separate date table. You would still want to break out of your data by Month or Year-Quarter or… whatever. You would still want to know if a particular date was a work day… or a Monday. And having this lookup table would make sense.
But the time intelligence functions require this table, and once you have it, you can do some pretty cool stuff. Well, technically you could do the same cool stuff without these functions — but using them is super convenient.
So, we have some measure, say… [Total Sales] := SUM(Sales[SweetCash]) and we have already related our Sales table to our Date table (on Sales[OrderDate] or similiar). And now we can write: [Sales YTD] := CALCULATE([Total Sales], DATESYTD(Calendar[DateKey]))
And easy as that… if you drop Year and Month from our Calendar table onto rows, and our new [Sales YTD] in values of our pivot… you would see Sales grow and grow… aggregating everything from Jan 1, 2003 to July 31, 2003 into the 2003,July cell. Easy cheezy.
A bit of an aside…
Realize that if you are doing say… the number of sales per day… you are probably going to need to think a bit. What is a “day” in that scenario? If its literally every day, then you can just divide by COUNTROWS(Calendar). But you probably do not want something like DISTINCTCOUNT(Sales[OrderDate]) because… what if there was a date where nobody ordered the product you are looking at? But it is likely what you REALLY want is the number of “Working” days — so you will probably have a column in your Calendar table that marks days that are, maybe Weekends and Holidays, so you can filter those out of your Sales per Day measure.
You can see where… even w/o the fun of calling DATESYTD() or SAMEPERIODLASTYEAR()… you were still going to need a Calendar table.
Back To It…
Easily the worst part of Date tables is there is no built in way to get one. You must already have one… or create one. There are an infinite number of ways to get a calendar table… SQL, VBA, static CSV files, Power Query, etc. But, I would love for there to be a cute button in Power Pivot “Create Calendar” but… alas. Search around and find something that works for you – it’s not really a show stopper. I sometimes just make a quick one in an Excel table and link that into my model.
Roughly 100% of the time — you Calendar table will not have JUST the required date column. Likely, it will also have a bunch of other useful columns… Year, MonthNumber, MonthName, Quarter, WeekdayName, IsWorkDay, etc. Again, plenty of articles, examples and sample code to help you here. I just want to make sure you understand the basic idea
So… Do you?
Scott, thank you!
What do you do with Running Total? Date Table is from 1/1/15 to 1/1/16
On columns there are dates from Date Table
There is a measure with Running Total
Today is 02 april 2015 and you want your last column in the Pivot Table will be 02 april 2015
please!
There is a DATESYTD() — Year to Date, that may work for you. And the definition of the first day of year can be changed by the 2nd param. But if you want a LifetimeToDate … you will need write that custom (search for it, you will find it :))
Scott, thank you!
the problem is not with DATESYTD, the problem is when you use DATESYTD as a measure and dates from DimDate on columns you’ve got all columns with data from start till the end
But today is 04 april and you need to restrict columns till 04 april
Opinions vary here. Rob Collie would trim his Calendar table to end on “today”. Ken Puls, I believe, doesn’t enjoy hard trimming the calendar table, so I assume ends up doing a bit of fancy IF() inside his to-date measures. Sometimes I will add a “IsFuture” column to my date table to help w/ filtering out future dates.
thank you very much!
Nice summary of the benefits of a Calendar table. Was thinking about why it’s good to mark it as a Date table in Powerpivot..I personally like the fact that it will sort the Months in chronological order if you sort the pivot table…otherwise you are sorting it based on a custom column?
Powerpivot newbie here..just my 2 cents.