Forum Discussion

tomekm's avatar
tomekm
Helper III
1 year ago
Solved

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

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...
Need Help
  • BITomS's avatar
    BITomS
    1 year ago

    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's avatar
    BITomS
    1 year ago

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

BITomS's avatar
BITomS
Solution Supplier
1 year ago

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's avatar
tomekm
Helper III
1 year ago

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's avatar
    BITomS
    Solution Supplier
    1 year ago

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