Rolling Weighted Average

Thanks for the responses!

amitchandak Unfortunately, trying to update the function similar to what you explained did not fix my issue. Looks like I still have the same problems with the the data.

Greg_Deckler My apologies if my original explanation wasn't clear. What I'm looking for is a way to calculate the two-year rolling average ratio of X / Y. For example, if I want the 2-Year X / Y for category C in the year 2003 then this would be (93+71) / (110+124) = 70% --> (XC for 2003 + XC for 2002) / (YC for 2003 + YC for 2002). I also want the ability to remove the A, B, C categories and have the 2-year X/Y ratio work properly for the year in total. Does that make sense?

Ashish_Mathur The file you provided looks great! Everything seems to be working properly. However, when I applied the same formulas to my actual data set I couldn't seem to get it to work. Adapting from the work you provided I used the following for a calculated column:

2-Year X/Y = IF( ISBLANK( CALCULATE( SUM('Data'[X]), DATEADD('Data'[Date],-2,YEAR) ) ), BLANK(), CALCULATE( SUM('Data'[X]), DATESBETWEEN('Data'[Date], EDATE('Data'[Date], -12), 'Data'[Date]) ) ) / CALCULATE( SUM('Data'[Y]), DATESBETWEEN('Data'[Date], EDATE('Data'[Date],-12), 'Data'[Date]) )

Can you critique my formula above? Everything is coming through blank making me think the first SUM()/DATEADD() is not working properly. I tried applying everything exactly as it was laid out in your original file at first (creating a calendar table, creating measures to sum X and Y, etc.) but when it wasn't working I played with it ending with what I have above. I used a SUM() function instead of creating a summing measure and the DATEADD() function instead of PREVIOUSYEAR() so that I could use it for periods other than 2 years if desired. Why is this not correct / a valid methodology?

Thanks!!