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