Forum Discussion
Issue With Rolling 3 Month Average
Solution SagedaXtreme 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
Of course it averages the last 3 months when you're on the year level. That's exactly what is expected. How else would you like such a measure to work? If you want it to work differently on each level (year, semester, month, day), then you have to use a different formula (with SWITCH-ing) but that's not what people would expect when they see a 3-month avg.
daXtreme So I guess my question is how do I get the measure to take the 3 month rolling average for each month like he does in the SQLBI link you sent? When I try to use the same measure the values at the month level are same in both the Net Revenue and the Rolling 3 Month Revenue columns - only the yearly amounts have changed. The end result I'm looking for is exactly what the end result in the SQLBI video is, mine just isn't coming out the same way.
- daXtreme
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.
