Forum Discussion

maxfefer's avatar
maxfefer
Regular Visitor
6 years ago

Help with rolling average

Hello,   I am having issues calculating a moving average. I think there may be something wrong with my Date column? When I try to calculate a 7 day rolling average in a new column "RollAvg1" with t...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

maxfefer , I typically used this kind of formula with date calendar

 

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day))

 

 

  • maxfefer's avatar
    maxfefer
    Regular Visitor
    6 years ago

    Thank you, amitchandak. The equation you provided blank results in my column. I also tried including the .[Date] suffix and it still resulted in blank results.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi maxfefer，

       

      Try below calculated column:

       

      RollAvg1 = 
    AVERAGEX (Filter('Query1',Query1[Date]<=earlier(Query1[Date])&&Query1[Date]>=earlier(Query1[Date])-6),
       
        CALCULATE ( SUM ( Query1[Values] ) )
    )

       

       

      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!
      • maxfefer's avatar
        maxfefer
        Regular Visitor
        6 years ago

        Thanks Kelly. This also didn't work since there is a circular logic error, probably from referencing Query1[Date] twice in the boolean. Is there a way to elevate this to Microsoft staff?