Backward moving average

[cocomy]

Hi All,

I am trying to do 7 days backward moving average for SALES[TOTAL] by [BRANCH]

SALES[TOTAL] does not contain ZERO when there is no sales. When I do moving average with the measure BELOW, it ignores.

Could you please help me to get backforward moving average , considering blank cell as zero?

Rolling average =
VAR select_date =
    MAX (CALENDAR[Date])
VAR end_Date = select_date - 7
VAR Branch_select =
    MAX (BRANCH[BRANCH])
RETURN
    CALCULATE (
        AVERAGEX (SALES, SALES[SALES_TOTAL]),
        FILTER (
            ALL (SALES),
            SALES[SALES_DATE] <= select_date
                && SALES[SALES_DATE] >= end_Date
                && SALES[BRANCH] = Branch_select
        )
    )

All the best,

cocomy

[Greg_Deckler · Accepted solution]

I would create a measure for SALES[Total] along the lines of:

IF(ISBLANK(SUM(SALES[TOTAL])),0,SUM(SALES[TOTAL))