Measure wont appear when multiplying rolling average to fixed number

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.