Forum Discussion

devleena's avatar
devleena
Frequent Visitor
8 years ago
Solved

Incorrect Moving Average Date and Sum

Hello PowerBI Gurus,   I know moving average is a fairly common question but I have some current issues with my DAX  formula. I will explain the data set I am using and the talk about the formula ...
Need Help
Show and Tell
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

devleena's avatar
devleena
Frequent Visitor
8 years ago

Hello Greg,

 

I tried your solution and a little RnD at my end too.

 

However, I am not being able to calculate the Moving Average as they get calculated in Microsoft Excel .

 

The Moving Average is being calculated correctly now, however I do not want to put the first 7 non-average days in the graph .

I can drop them ( exclude rows ) manually,  however, it will not really solve my case , because the start date ( earliest date ) of my dataset varies .

 

So, say when I am calculating the 7  day moving average for a store in Location A, the earliest day is Nov 13th , 2016 .

The average days start from Nov 19th ( 7 days from start ) and I am expecting my result to output only days from Nov 19th and onwards.

 

I also have a different store in a different location, the earliest day of which is  Jan 22nd, 2017, so the 7th day will be different.

 

 

Please help on what is the best solution to this.

 

 

Thanks and Regards,

Dev

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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