Calculation Issues

Fix Excel Power Pivot SAMEPERIODLASTYEAR Returning a Value Instead of Blank

Amos GikundaAmos Gikunda Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the comparison measure

In Excel, select Power Pivot > Measures > Manage Measures, then select the affected comparison measure and choose Edit.

2
Add the blank check

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.

3
Check the PivotTable

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.

4
Update related measures

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.

Check the prior-year measure before calculating the comparison
Check the condition measure: This pattern suppresses the comparison only when the measure inside ISBLANK returns a blank in the current filter context. If it returns zero or another value, review that measure and the PivotTable filters.
Free Microsoft Office alternative

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.

Open and edit spreadsheet data in WPS SpreadsheetsCompatible with Microsoft Excel XLS and XLSX formatsFamiliar spreadsheet interface for everyday workLightweight tools for working with Office documents
microsoft office alternative - wps office

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.