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,

     

    Thank you for the prompt reply .

     

    Please find the text data below 

     

    DateTotal Sales7 Day Rolling Avg
    13/11/15 0:00₹31,650 
    14/11/15 0:00₹1,07,740 
    15/11/15 0:00₹1,38,140 
    16/11/15 0:00₹52,370 
    17/11/15 0:00₹71,570 
    18/11/15 0:00₹1,25,490 
    19/11/15 0:00₹88,890₹6,15,850
    20/11/15 0:00₹1,50,490₹7,34,690
    21/11/15 0:00₹1,50,520₹7,77,470
    23/11/15 0:00₹83,270₹7,22,600
    24/11/15 0:00₹1,15,200₹7,85,430
    25/11/15 0:00₹89,170₹8,03,030
    26/11/15 0:00₹76,920₹7,54,460
    27/11/15 0:00₹2,12,510₹8,78,080
    28/11/15 0:00₹1,51,440₹8,79,030
    29/11/15 0:00₹1,46,350₹8,74,860
    30/11/15 0:00₹77,640₹8,69,230
    01/12/15 0:00₹65,970₹8,20,000
    02/12/15 0:00₹1,24,530₹8,55,360
    03/12/15 0:00₹87,100₹8,65,540
    04/12/15 0:00₹1,57,820₹8,10,850
    05/12/15 0:00₹2,05,070₹8,64,480
    06/12/15 0:00₹1,19,550₹8,37,680
    07/12/15 0:00₹87,170₹8,47,210
    08/12/15 0:00₹93,570₹8,74,810
    09/12/15 0:00₹1,50,260₹9,00,540
    10/12/15 0:00₹1,49,450₹9,62,890
    11/12/15 0:00₹2,36,620₹10,41,690
    12/12/15 0:00₹1,86,540₹10,23,160
    13/12/15 0:00₹1,37,470₹10,41,080

     

    Hope this helps 

     

    Thanks and Regards,

    Dev

  • devleena's avatar
    devleena
    Frequent Visitor
    8 years ago

    The amount being calculated is correct . There is a gap in the record , 22nd Novemeber 2015 is not there.

    So I switched on the key for showing null values and the math is now working fine.

     

    What I still not being able to do is, ignore the first 7 days where there is no rollup sum. 

  • 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
      Icon for Microsoft Employee rankMicrosoft 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