Fix Excel Power Pivot SAMEPERIODLASTYEAR Returning a Value Instead of Blank
Question details
Prevent a prior-year comparison measure from returning a value when the prior-year quantity measure is blank.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- A PivotTable compares a three-month average with the same period in the prior year, including dates in the first year of the data.
- Observed behavior
- The SAMEPERIODLASTYEAR comparison returns a value for first-year dates even though another prior-year measure shows blanks.
Identify the prior-year quantity measure and the three-month average measure in the Power Pivot model. Confirm that the calendar date column used in the formula is the one used for the PivotTable's date context.
Check the prior-year measure before calculating the comparison
Use this conditional DAX pattern to return a blank whenever the prior-year quantity measure is blank.
The IF and ISBLANK check uses the existing prior-year quantity measure as the condition. When that measure has a value, CALCULATE evaluates the three-month average for the same period last year.
In Excel, select Power Pivot > Measures > Manage Measures, then select the affected comparison measure and choose Edit.
Replace the measure formula with: =IF(ISBLANK([Total Quantity PY]),BLANK(),CALCULATE([Total Quantity 3MM Avg],SAMEPERIODLASTYEAR(dimCALENDAR[Date]))). Use your model's actual measure and calendar column names if they differ.
Save the measure and return to the PivotTable. Check first-year dates where [Total Quantity PY] is blank and confirm that the comparison measure is also blank.
In Power Pivot > Measures > Manage Measures, apply the same IF(ISBLANK(...),BLANK(),...) pattern to related cost comparison measures, using the corresponding prior-year cost measure as the condition.

Work with spreadsheet files in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility for an easier transition. Use Excel Power Pivot to edit and troubleshoot the DAX measure described above.

Frequently Asked Questions
Why does the comparison measure return a value when the prior-year quantity is blank?
The three-month average comparison and the prior-year quantity are separate measures and can evaluate differently. Add an ISBLANK check on [Total Quantity PY] before calculating the comparison if that measure should control whether a result appears.
Does ISBLANK treat zero as a blank?
No. ISBLANK tests for a blank value, not zero. If [Total Quantity PY] returns zero, the formula continues to calculate the comparison.
Where can I edit a Power Pivot measure?
In Excel, go to Power Pivot > Measures > Manage Measures, select the measure, and choose Edit. Update the formula and check the result in the PivotTable.
Can I use the same fix for a prior-year cost measure?
Yes. Wrap the cost comparison calculation in IF(ISBLANK([Prior-Year Cost Measure]),BLANK(),...), replacing the example name with the corresponding measure in your model.




