DAX Measure for Filling Blanks with Last Available Value

[VannurVali]

Hello everyone,

I need some suggestions  to achieve to fill blank values with last available date value. Below is my sample table.

Date	Month	Store_ID	Product_Code	Sales
01-01-2024	1	A	101	100
01-02-2024	2	A	101
01-03-2024	3	A	101	400
01-01-2024	1	B	102	300
01-02-2024	2	B	102
01-03-2024	3	B	102	500

Expected Behavior:

• For February 2024 for Store A and Product 101, if the Sales is blank, it will take 100 (the Sales value from January 2024).
• For March 2024 for Store B and Product 102, if the Sales is blank, it will take 500 (the Sales value from February 2024)

I need to calculate in the meausre please help on this .

Note : above table is just for your reference we don't have dates for blank rows we are taking from dim_calendar. we have data in below format in my fact table

Date	Month	Store_ID	Product_Code	Sales
01-01-2024	1	A	101	100
01-03-2024	3	A	101	400
01-01-2024	1	B	102	300
01-03-2024	3	B	102	500

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[VannurVali]

Hi @Ashish_Mathur ,
Thanks for the quick response. It's working now, but I need to highlight the missing months in color.
Thanks!

[Ashish_Mathur]

You are welcome.  See this image

[danextian]

It is actually in my sample pbix.

[danextian]

Hi @VannurVali 

Please try the following:

Last Nonblank Value = 
SUMX (
    SUMMARIZECOLUMNS (
        Dates[Year],
        Dates[Month],
        'Table'[Store_ID],
        'Table'[Product_Code],
        "@value",
            IF (
                NOT ( ISBLANK ( [Sum of Sales] ) ),
                [Sum of Sales],
                CALCULATE (
                    LASTNONBLANKVALUE ( Dates[Month], [Sum of Sales] ),
                    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
                )
            )
    ),
    [@value]
)

The cells highlighted in yellow are the latest values before the current month.

Please see the attached sample pbix.

[DataNinja777]

Hi @VannurVali ,

To ensure optimal performance and maintainability, we will structure the data using a star schema. This means separating transaction-level data (FactSales) from descriptive information (DimDate, DimProduct, DimStore). The DimDate table is created using:

DimDate = ADDCOLUMNS (
    CALENDAR (DATE(2023,1,1), DATE(2025,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date])
)

With this setup, FactSales relates to DimDate through FactSales[Date] = DimDate[Date], ensuring that all time-based calculations are handled consistently.

Now, to fill missing sales values using the last available sales amount, we rewrite the DAX measure to fully respect the star schema by filtering based on DimDate[Date] instead of FactSales[Date]. This approach ensures that the measure is optimized for performance and properly leverages the date hierarchy.

Filled Sales = 
VAR LastSale =
    CALCULATE(
        MAX(FactSales[Sales]),
        FILTER(
            ALL(DimDate),
            DimDate[Date] < SELECTEDVALUE(DimDate[Date])
        ),
        FactSales[Store_ID] = SELECTEDVALUE(FactSales[Store_ID]),
        FactSales[Product_Code] = SELECTEDVALUE(FactSales[Product_Code]),
        NOT(ISBLANK(FactSales[Sales]))
    )
RETURN
    IF(
        ISBLANK(SELECTEDVALUE(FactSales[Sales])),
        LastSale,
        SELECTEDVALUE(FactSales[Sales])
    )

By filtering DimDate rather than FactSales, this measure properly aligns with the star schema. It ensures that missing sales values are filled using the last available value while keeping the calculations efficient. This also allows for better time intelligence integration and smoother performance in Power BI.

Best regards,