Forum Discussion

JustineTennent's avatar
JustineTennent
New Member
8 years ago

Quick Measure - 7 day rolling average excluding 0's

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 movin...
JustineTennent's avatar
JustineTennent
New Member
8 years ago

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

 

thanks for trying tho!

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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