Measure wont appear when multiplying rolling average to fixed number

[non23]

I would like to multiply the 2nd table to kilos base on it's category. Unfortunately, since 11/07/2025 orginally doesn't have a data it won't show the kg convertion. 

Current measure that I have is:

Measure = CALCULATE(SUMX(Sales,[2-Day Rolling Average]*RELATED('Weight'[KG])))

Current relationship is based on the category of Table1 and Table2

Hoping to show 4800 as well for 11/07/2025 on the third table and the 14/07/2025 data as well.

[danextian · Accepted solution]

Hi @non23 

That’s likely because the category refers to a row that doesn’t exist. For example, on November 7, a specific category might not have an entry in your data. So while the rolling average returns a value, there’s no corresponding value for the conversion factor.

A more reliable approach would be to use dimension tables—one for dates and one for categories. The date dimension should contain all dates without gaps. Then, base your calculations on those dimension tables using the relevant columns. Examples:

Rolling Avg Sales =
CALCULATE (
    AVERAGEX ( VALUES ( Dates[Date] ), [Sum of Sales] ),
    DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -2, DAY ),
    REMOVEFILTERS ( Dates ) --- not necessary if Dates have been marked as date table.
)

Rolling Avg Sales Converted =
SUMX (
    SUMMARIZECOLUMNS (
        Dates[Date],
        ConversionFactor[Category],
        "@converted", [Rolling Avg Sales] * CALCULATE ( SUM ( ConversionFactor[ConversionFactor] ) )
    ),
    [@converted]
)

You can see in the sc reenshot below that as long as rolling average has a value, so does the converted.

Please see the attached pbix.