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...
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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'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