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...
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?  

     

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

      Share some data and show the expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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