Incorrect Moving Average Date and Sum

Hi devleena,

Please try this solution to calculate moving average without adding a calendar table.

In Query Editor mode, sort the field 'DoolallySales doolally_pos_master_staging'[pos_order_date] in Ascending order. Then, add an index column.

Then, in report view, add DoolallySales doolally_pos_master_staging'[pos_order_date] into table visual, create measues like this:

TotalSales = SUM([final_total])

Rolling 7 day SUM2 =
IF (
    MAX ( doolally_pos_master_staging[Index] ) < 7,
    BLANK (),
    CALCULATE (
        [TotalSales],
        FILTER (
            ALL ( doolally_pos_master_staging ),
            doolally_pos_master_staging[Index] <= MAX ( doolally_pos_master_staging[Index] )
                && doolally_pos_master_staging[Index]
                    > MAX ( doolally_pos_master_staging[Index] ) - 7
        )
    )
)

Rolling 7 day Average2 =
IF (
    MAX ( doolally_pos_master_staging[Index] ) < 7,
    BLANK (),
    CALCULATE (
        [TotalSales],
        FILTER (
            ALL ( doolally_pos_master_staging ),
            doolally_pos_master_staging[Index] <= MAX ( doolally_pos_master_staging[Index] )
                && doolally_pos_master_staging[Index]
                    > MAX ( doolally_pos_master_staging[Index] ) - 7
        )
    )
)
    / 7

Best regards,

Yuliana Gu