Tiny Lizard

Power Pivot Consulting and Training

  • About
  • Blog
  • Power Pivot
  • Training
  • Contact

Power Pivot Date Table

April 2, 2015 By Scott Senkeresty 6 Comments

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 Smile

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?

Note we are only talking about really traditional “Wall Calendar” type stuff.  If you are running a 445 Calendar, all bets are off.  The built in functions won’t help you… at all.

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.

image

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 Smile

So… Do you?

Many-to-Many Relationships
The DAX EARLIER Function

Filed Under: Fundamentals

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 277 other subscribers

Comments

  1. 100tsky says

    April 2, 2015 at 7:35 am

    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!

    Reply
    • Scott Senkeresty says

      April 2, 2015 at 4:12 pm

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

      Reply
      • 100tsky says

        April 4, 2015 at 3:05 am

        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

        Reply
        • Scott Senkeresty says

          April 4, 2015 at 5:13 pm

          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.

          Reply
          • 100tsky says

            April 5, 2015 at 12:17 am

            thank you very much!

  2. TheOlTravi says

    April 3, 2015 at 10:54 pm

    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.

    Reply

Leave a Reply Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Blog Posts

  • The streak is alive! August 10, 2017
  • DAX KEEPFILTERS Plus Bonus Complaining! July 20, 2017
  • VAR: The best thing to happen to DAX since CALCULATE() July 11, 2017
  • Review: Analyzing Data with Power BI and Power Pivot for Excel June 20, 2017
  • Power BI Date Table June 2, 2017

Follow on Twitter

My Tweets

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 277 other subscribers

Copyright © 2021 Tiny Lizard