Yearly rolling window over multiple years, depending on which Year is selected in slicer

[tomekm]

Hello,

I'm trying to come up with a formula to calculate a 4 year rolling window, so that when the slicer selects 2025, it computes the rolling average (variant 1 divided by variant 2) over: 2025, 2024, 2023, 2022. And if slicer = 2024, it computes the average for 2024,2023,2022,2021, etc. etc. For 2025 it equals: 109/29.5=3.69. Thank you.

Here's my data:

variant 1	variant 2	Year
11	1	2017
2	2	2018
5	2.2	2019
6	1.8	2020
9	7.2	2021
32	3	2022
2	0.5	2023
67	22	2024
8	4	2025

[BITomS · Accepted solution]

RollingAverage =
VAR SelectedYear = SELECTEDVALUE('Table'[Year])
VAR StartYear = SelectedYear - 3

VAR SumVar1 =
CALCULATE(
[Variant 1 Measure],
FILTER(ALL('Table'), 'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear)
)

VAR SumVar2 =
CALCULATE(
[Variant 2 Measure],
FILTER(ALL('Table'), 'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear)
)

RETURN
IF(SumVar2 <> 0, SumVar1 / SumVar2, BLANK())

[BITomS]

Hi @tomekm ,

RollingAverage =
VAR SelectedYear = SELECTEDVALUE('Table'[Year])
VAR StartYear = SelectedYear - 3
VAR SumVar1 = CALCULATE(
SUM('Table'[variant 1]),
'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear
)
VAR SumVar2 = CALCULATE(
SUM('Table'[variant 2]),
'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear
)
RETURN
IF(SumVar2 <> 0, SumVar1 / SumVar2, BLANK())

Hope this helps!

[tomekm]

Thank you, but my variant 1 and variant 2 are actually measures, and not calculated columns, so this approach won't work I believe.

[BITomS · Accepted solution]

RollingAverage =
VAR SelectedYear = SELECTEDVALUE('Table'[Year])
VAR StartYear = SelectedYear - 3

VAR SumVar1 =
CALCULATE(
[Variant 1 Measure],
FILTER(ALL('Table'), 'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear)
)

VAR SumVar2 =
CALCULATE(
[Variant 2 Measure],
FILTER(ALL('Table'), 'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear)
)

RETURN
IF(SumVar2 <> 0, SumVar1 / SumVar2, BLANK())

[tomekm]

Thanks but when I add this measure to my table and charts, it is showing blank for all the yearly values; something is wrong. Not sure what..

[BITomS · Accepted solution]

SELECTEDVALUE('Table'[Year]) only applies if you are selecting a single year i.e. from a slicer, which it sounded like you were. If this isn't the case try this:

RollingAverage =
VAR SelectedYear = MAX('Table'[Year]) -- Gets the year in the current row context
VAR StartYear = SelectedYear - 3

VAR SumVar1 =
CALCULATE(
[Variant 1 Measure], -- Uses the existing measure
'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear
)

VAR SumVar2 =
CALCULATE(
[Variant 2 Measure], -- Uses the existing measure
'Table'[Year] >= StartYear && 'Table'[Year] <= SelectedYear
)

RETURN
IF(SumVar2 <> 0, SumVar1 / SumVar2, BLANK())