Quick Measure - 7 day rolling average excluding 0's

[JustineTennent]

Hi All,

I've added a quick measure to calculate the 7 day rolling average of some data--- problem is where a record had a value of 0 it is excluded from the average therefore increasing the moving average value and inturn making it incorrect

ie.

20 1/1/18

30 2/1/18

30 3/1/18

0 4/1/18

40 5/1/18

40 6/1/18

40 7/118

50 8/1/18

= moving average value for the  4/1/18 should be 28.57 instead its 33 (only using 6 values instead?) 

How can i fix the measure to include the data that has 0's

GARI TOTAL rolling average =
IF(
ISFILTERED('GARI Summary MOD'[LOAD_DATE]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = LASTDATE('GARI Summary MOD'[LOAD_DATE].[Date])
RETURN
AVERAGEX(
DATESBETWEEN(
'GARI Summary MOD'[LOAD_DATE].[Date],
DATEADD(__LAST_DATE, -3, DAY),
DATEADD(__LAST_DATE, 3, DAY)
),
CALCULATE(SUM('GARI Summary MOD'[GARI TOTAL]))
)
)

[Ashish_Mathur]

Hi,

Here's what i would suggest:

1. Create a Calendar Table with running dates from the first date till the last date that appear in the Date column of your source dataset.  You may create this Calendar Table by going to Modelling > New Table and writing the following formula = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))
2. Create a relationship between the Date column of the Data Table to the Date column of the Calendar Table
3. In your visual, drag the Date column from the Calendar Table
4. Write the followng measure

=CALCULATE(AVERAGE(Data[Value]),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-7,MAX(Calendar[Date])))

Hope this helps.

[realdanielbyrne]

@Ashish_Mathur that method does not work.  Rolling average are still based upon the days with entries when filtered thus inflating the value.  For example here is some data from a pbix I am working on.

14 Day Average = 
    AVERAGEX(DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14,DAY),[Total Sales])

Date range 8/13 - 8/31

And here are the results :

Equipment	CostCode	Date	Total Sales
BD11007	1003-2	8/31/2018 0:00	$310
BD11007	1003-2	8/25/2018 0:00	$310
BD11007	1003-2	8/18/2018 0:00	$465

Equipment	Sales MTD	14 Day  Average
BD11007	$1,086	$362

The date table in this instance is linked to the sales table as you suggested, but this calculation still filters out days with no sales in the denominator.  The average should be  $1086 / 14 days = $76

but Dax is reporting  $1086 /  3 = $362

I wonder how many Power BI viz out there are incorrect because no one bothered to check the math?  

[Anonymous]

Hi @realdanielbyrne 

I was wondering if you had this issue solved, if so please share the solution.I am having the same problem in getting the moving average for the past 25 days.

kind regards,

HiltonM

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Phil_Seamark]

Hi @JustineTennent

You could try this technique  I've highlighted the changes in red.

GARI TOTAL rolling average = 
IF(
ISFILTERED('GARI Summary MOD'[LOAD_DATE]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = LASTDATE('GARI Summary MOD'[LOAD_DATE].[Date])
RETURN
    SUMX(
        DATESBETWEEN(
            'GARI Summary MOD'[LOAD_DATE].[Date],
            DATEADD(__LAST_DATE, -3, DAY),
            DATEADD(__LAST_DATE, 3, DAY)
        ),
        CALCULATE(SUM('GARI Summary MOD'[GARI TOTAL]))
    ) /7
)

[JustineTennent]

Unfortunately your answer divides the original sample value by 7 rather than the sum of 7 days 😞

thanks for trying tho!

[Phil_Seamark]

HI @JustineTennent

Looks like I had it too early in the formula.

Please try this.

GARI TOTAL rolling average = 
IF(
ISFILTERED('GARI Summary MOD'[LOAD_DATE]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = LASTDATE('GARI Summary MOD'[LOAD_DATE].[Date])
RETURN
    SUMX(
        DATESBETWEEN(
            'GARI Summary MOD'[LOAD_DATE].[Date],
            DATEADD(__LAST_DATE, -3, DAY),
            DATEADD(__LAST_DATE, 3, DAY)
        ),
        CALCULATE(SUM('GARI Summary MOD'[GARI TOTAL]))
    ) 
)/7