Power Pivot SAMEPERIODLASTYEAR

Question

I've got this pivot (below).  Data is from 1/1/2018 and later.  Using SAMEPERIODLASTYEAR function, I get the expected results for Total Quantity Previous Year in column D.  2018 values are blank as expected because there is no 2017 data.  Same logic applied to column G, but I get 105,131.97 for a 2018 value when I expected blanks.  That number is the amount on the specific date 1/1/2018.

Formula looks like this:  =CALCULATE([Total Quantity 3MM Avg],SAMEPERIODLASTYEAR(dimCALENDAR[Date])).

Why can't I get those blanks to appear in column G like I can in column D?

![](https://learn-attachment.microsoft.com/api/attachments/51302e8b-da44-41ff-a5af-2ec28c09332f?platform=QnA

Answer 1

Hi,

One simple way out is to use this measure for Total Quantity 3MM PY Avg (see column F in the image below).

=if(ISBLANK([Total Quantity PY]),BLANK(),CALCULATE([Total Quantity 3MM Avg],SAMEPERIODLASTYEAR(dimCALENDAR[Date])))

Do the same for the Cost measure as well

Hope this helps.

Answer 2

Perfect, thank you!

Answer 3

Worksheet called PivotTable is where you can see the issue in pivot table format.

Answer 4

Link to zipped file:  https://1drv.ms/u/s!AiCGN7G0L7PamDOE5o6KDqhsiGFA?e=esanHb

Answer 5

Hi,

Share the link from where I can download the Excel file.