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