Forum Discussion

newguy's avatar
newguy
New Member
4 years ago
Solved

Issue With Rolling 3 Month Average

I know there are alot of rolling average posts out there, but I haven't been able to find a solution to my issue. I am trying to get a 3 month rolling average of revenue, example below:   For...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi newguy 
    Would you please double check and confirm if the date table is marked as date table. In all cases would you please add REMOVEFILTERS ( D_Date ) inside CALCULATE and let me know if you get any different results. 

newguy's avatar
newguy
New Member
4 years ago

daXtreme Thats actually the video that I used to get where I am at now. I copied the DAX from the SQLBI website and changed it to use my specific tables and measure. I just can't figure out why it would only average the last 3 months for each year, but not also work for each individual month like it does in the SQLBI video. I've tried using other measures, removing the other joins to the date table so that there is only one active relationship and nothing seems to be working. Thank you for the response!

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

If you want this to only work on a full month level, then you'll have to detect that you are exactly on this level, and then apply the formula. So, first you have to see if in the current context only one year-month is visible and if it has exactly as many days as the year-month when there are no filters. If this is true, then apply the formula. This way you'll get rid of the average from the year level and any other level than year-month.