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 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]))
)
)

 

 

 

 

7 Replies

  • 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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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's avatar
      realdanielbyrne
      Frequent Visitor
      7 years ago

      hat 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 :

       

      EquipmentCostCodeDateTotal Sales
      BD110071003-28/31/2018 0:00$310
      BD110071003-28/25/2018 0:00$310
      BD110071003-28/18/2018 0:00$465

       

      EquipmentSales MTD14 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?