How to Fill Blank Values with the Previous Value Using DAX in Excel
Question details
The user needs to populate empty sums in a calculated table by carrying forward the sum values from the previous month using DAX.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing time-series data in Power Pivot or Power Query where certain months lack values, and the previous available month's sum needs to be carried forward to maintain continuity.
- Observed behavior
- Certain rows contain blank values instead of carrying over the previous month's data, which requires a specific data model structure, sort order, and a targeted DAX formula or Power Query transformation to resolve.
Ensure your data model includes a well-defined Date or Year-Month column properly formatted as a Date type, as DAX time-intelligence calculations require a reliable chronological filter context.
Use the Fill Down Feature in Power Query
If you are preparing your data before loading it into the data model, Power Query offers a built-in tool that easily replaces blank values with the last non-empty value, avoiding complex DAX.
Using Power Query to clean up static blank data is generally more efficient than computing it dynamically with DAX. This method permanently fills the empty cells in the data model so that your DAX measures remain simple.
Select your data table in Excel, navigate to the 'Data' tab, and click 'From Table/Range' to open the Power Query Editor.
Click on the header of the column containing the blank values (sums) that you want to fill.
Go to the 'Transform' tab on the top ribbon, click on the 'Fill' dropdown, and select 'Down'. This will replace all null values with the value immediately above them.
Click 'Close & Load' to bring the cleaned data back into your Excel worksheet or directly into your Power Pivot Data Model.
Create a DAX Measure to Fetch the Previous Non-Blank Value
If the data must be calculated dynamically based on user selections in a PivotTable, use DAX variables and the CALCULATE function to locate the last available value.
Streamline Your Data Analysis with WPS Office
While advanced DAX environments like Power Pivot are specific to the Microsoft ecosystem, many data cleaning and analysis tasks can be handled much faster using intuitive spreadsheet features. WPS Office provides powerful, lightweight data tools and seamless compatibility with Excel files.
- 1. Open Your Data File: Launch WPS Spreadsheet and open your existing .xlsx dataset.
- 2. Select the Blanks: Highlight your data column, press Ctrl+G to open the 'Go To' dialog, select 'Blanks', and click OK.
- 3. Fill with Previous Values: Type an equals sign (=), press the Up Arrow key to reference the cell directly above, and press Ctrl+Enter to instantly fill all blanks.
Frequently Asked Questions
Why is my DAX formula returning a blank instead of the previous month's value?
This usually happens due to an issue with the filter context or if your Date table is not properly linked to your fact table. Ensure your Year-Month column is formatted correctly as a date type and that the relationships in your data model are active.
Should I use Power Query or DAX to fill blank values?
If the blank values are static and just need to be cleaned up before analysis, Power Query's 'Fill Down' feature is much simpler and improves performance. Use DAX only if the values need to be dynamically calculated based on changing user selections in a dashboard.
Can I fill blank values with the previous cell's data without using DAX or Power Query?
Yes, you can use the native spreadsheet tools. Select your data range, press Ctrl+G to open the Go To dialog, select 'Special' and then 'Blanks'. Type an equals sign (=), press the Up arrow key, and press Ctrl+Enter to fill all empty cells with the value immediately above them.
