cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
devleena Frequent Visitor
Frequent Visitor

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 .

 

Say I have the following sample, the right most corner is the Rolling 7 day average.

 

So, basically if i have the following sets , the 3rd column ( 7 day rolling average) is populated from the 7th day observation and is the sum of the last 7 days .

The next value is the sum of the 2nd day to 7th day , and so on. The following is a screenshot of the formula worked out on excel.

 

Screen Shot 2018-07-11 at 5.45.32 PM.png

 

I am using the following DAX formula for the Moving Average calculation, I need both SUM and Average, so the following 

 

 

Rolling 7 day SUM = SUMX(DATESINPERIOD('Date Key'[Date], LASTDATE('Date Key'[Date]),-7,DAY),[Total Sales])

 

Rolling 7 day MA = AVERAGEX(DATESINPERIOD('Date Key'[Date], LASTDATE('Date Key'[Date]),-7,DAY),[Total Sales])

 

The output of "Rolling 7 day SUM" is the following in the DAX for formula used above ( SUMX(DATESINPERIOD('Date Key'[Date], LASTDATE('Date Key'[Date]),-7,DAY),[Total Sales]))

 

 

Screen Shot 2018-07-11 at 5.56.45 PM.png

 

You will see the first three rows for the 7 day match but then the next does not .

 

My table structure is ,

 

I have a main table named as  doolally_pos_master_staging

which has a date column named "pos_order_date" and a  sales column name "final_total"

 

and, TotalSales = SUM([final_total])

 

 

and,   Date Key is a Date table on the doolally_pos_master_staging table

 

Date Key = CALENDAR(min('DoolallySales doolally_pos_master_staging'[pos_order_date]), max('DoolallySales doolally_pos_master_staging'[pos_order_date]))

 

What am I doing wrong  and what should my formula be to ignore the first 7 days that does not have the rollup sum or rollup average.

 

Thanks you very much for your help.

 

Regards,

Dev

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: 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.

1.PNG2.PNG

 

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

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Incorrect Moving Average Date and Sum

Hmm, not sure what is going on but try posting your data as text that can be copied. Also, you might look at my Time Intelligence The Hard Way Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


devleena Frequent Visitor
Frequent Visitor

Re: Incorrect Moving Average Date and Sum

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 Frequent Visitor
Frequent Visitor

Re: Incorrect Moving Average Date and Sum

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 Frequent Visitor
Frequent Visitor

Re: Incorrect Moving Average Date and Sum

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

Community Support Team
Community Support Team

Re: 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.

1.PNG2.PNG

 

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

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.