Forum Discussion
Rolling Weighted Average
I'm looking for help in trying to calculate a rolling weighted average in my dataset. Below is an example of the data I'm working with:
| Category | Year | X | Y | 1-Year X/Y | 2-Year X/Y |
| A | 2000 | 83 | 166 | 50% | |
| B | 2000 | 86 | 153 | 56% | |
| C | 2000 | 89 | 124 | 72% | |
| A | 2001 | 73 | 126 | 58% | 53% |
| B | 2001 | 88 | 162 | 54% | 55% |
| C | 2001 | 68 | 156 | 44% | 56% |
| A | 2002 | 85 | 122 | 70% | 64% |
| B | 2002 | 90 | 174 | 52% | 53% |
| C | 2002 | 71 | 124 | 57% | 50% |
| A | 2003 | 91 | 138 | 66% | 68% |
| B | 2003 | 100 | 135 | 74% | 61% |
| C | 2003 | 93 | 110 | 85% | 70% |
| A | 2004 | 86 | 159 | 54% | 60% |
| B | 2004 | 61 | 102 | 60% | 68% |
| C | 2004 | 85 | 126 | 67% | 75% |
The 2-Year X/Y is what I'm trying to calculate. Reading other posts in these forums, I came up with the following strategy. I created a new column called "Date" which is
9 Replies
One of the date to refer to you fact date for reference of rollup
I typically does it like this
Rolling 2 year = CALCULATE( DIVIDE( SUM('Data'[X]), SUM('Data'[Y]) ,DATESINPERIOD('Date'[Date],ENDOFMONTH(Data[Date]),-2,Year))
Rolling 2 year = CALCULATE( DIVIDE( SUM('Data'[X]), SUM('Data'[Y]) ,DATESINPERIOD('Date'[Date Filer],MAX(Data[Date]),-2,Year))OK, walk me through what is supposed to happen in order to get your desired numbers. So how is that 53% supposed to be calculated? In just plain language.
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!!
Hi TrentSP ,
Measure 3 = VAR x = CALCULATE( [X values], DATEADD('Calendar'[Date], -2, YEAR) ) RETURN IF( x = BLANK(), BLANK(), CALCULATE([X values], DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-12),MAX('Calendar'[Date]))) / CALCULATE([y values],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-12),MAX('Calendar'[Date]))) )I can get the correct result if I use DATEADD() function.
If you use Ashish_Mathur 's formula, you must create a calendar table and the [year] column is from calendar table.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Mine's a measure (not a calculated column formula).
I also suggested a measure. So in case, it is used as a column it will not work. Anyway, Ashish_Mathur , has provided a working solution. Test that as measure and let us know.
Thank you to everyone who replied, the issue is resolved on my end now! The reason why the solutions weren't working for me at first was because of an issue with how I was linking the "Calendar" table to the "Data" table. But this is now fixed and the results look great!
You are welcome.
