Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too!

 
Year to Date (YTD) PowerPivot Measure (Calculated Field in 2013) is Returning Blanks and Prior Month (Previous Month, Last Month, etc.) is returning the same results as the original Sales measure.

The Pivot On the Left is Correct and the Pivot on the Right is Not.
But the Formulas Are the Same in BOTH Pivots!

A Semi-Common Question

From time to time, someone asks me a question of the following flavor:

“I’ve followed all of your advice on writing a ‘Year to Date’ Sales measure.  I’ve got a Calendar table and my DATESYTD function uses the Date column from that table.  But I am getting blank values for my YTD Sales measure and my Prior Month Sales measure is incorrect too.”

It Might Not Be Your Formulas!

In both pivots above, the formulas are identical:

[Last Month Sales]=
CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, Month))

[Total Sales YTD]=
CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

So what’s the problem in that pivot on the right?


Never Use Date Fields from Your Sales Table on the Pivot!

The problem is that I used the “Year Month” field from the Sales table in the pivot on the right:

If Your PowerPivot Formulas are Correct But You Use Date Fields from Your Sales (aka “Data”) Table on the Pivot, They Won’t Work.  Use Date-Related Fields from Your Calendar/Dates Table ONLY.

If Your Formulas are Correct But You Use Date Fields from Your Sales (aka “Data”) Table on the Pivot, They Won’t Work.  Use Date-Related Fields from Your Calendar/Dates Table ONLY.

So yeah, this boils down to a simple rule.  NEVER, EVER USE DATE FIELDS FROM YOUR ORIGINAL SALES TABLE ON YOUR PIVOT!  Not on Rows, not on Columns, not on Slicers or Report Filters.

Your Calendar table is not just there to use in your formulas (although it is very much necessary for that).  It’s also to be used on your pivot.  Don’t cheat Smile

Impacts “Life to Date” and Other Measures Too

Using date fields from your Sales table in conjunction with time navigation formulas is problematic in general – this problem is NOT limited to Prior Month and YTD.

For instance, a “Lifetime to Date” measure doesn’t work either:

Lifetime to Date (LTD) PowerPivot Measure ("Calculated Field" in 2013) is Returning the same results as the original Sales measure.

Basically ANY Time Navigation Formula Can Be “Broken” if You
Use Date Fields from the Sales Table on the Pivot

More General Version of the Rule

In general, if you have a “lookup” table (of which Calendar is just an example), use it in your formulas AND on your pivot.  If you have the same exact column in your data table and in your lookup table (say, CustomerID), use the one from the lookup table in your pivots AND in your formulas.  Pretend the version in the data table doesn’t exist. 

You can’t go wrong using the version from the Lookup table, but using the columns from the Data table can cause problems.  Sometimes it just can be slower to recalculate.  Other times it yields the wrong result.  Lookup tables are your friends.

All of this is covered in chapters 10 and 14 of the book of course, but I thought it would be worth a post today that reinforces it.

3 Responses to Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too!

  1. Bob says:

    It’s best practice to hide those columns in your Fact table which are used to link to Dimension tables if you have report consumer

  2. Ben Niebuhr says:

    Excellent comment, Bob. I have done this to myself enough times that I now default to hiding everything on my data tables from pivottables and useing things like =sum(table[item]) and =average(table[item]) rather then putting raw columns into the value field. This has saved me TONS of time trying to figure out which of my favorite errors I have added to the workbook.
    As an added bonus I am already set up to use disconnected tables to switch my analysis from sum to average within measures!

Leave a Comment or Question