Rolling Weighted Average

[TrentSP]

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 

Date = DATE('Data'[Year], 1, 1)

I'm using this as the start of the period for the rolling average. Then I created another calculated column defined as

2-Year X/Y = CALCULATE( DIVIDE( SUM('Data'[X]), SUM('Data'[Y]) ), DATESBETWEEN('Data'[Date], 'Data'[Date], 'Data'[Date]+2*366) )

When I use this approach the results aren't coming out how I'd expect. There is definitely an issue with the way I'm calculating the 2-Year X/Y. My guess is that my issue is with how I'm using the DATESBETWEEN() function? I'm fairly new to BI so any advice here would be appreciated!

[TrentSP]

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!

[Ashish_Mathur]

You are welcome.

[TrentSP]

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

[v-lionel-msft]

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@TrentSP 

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.

[Ashish_Mathur]

Hi,

Mine's a measure (not a calculated column formula).

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Greg_Deckler]

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.

[amitchandak]

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