Forum Discussion

tomekm's avatar
tomekm
Icon for Helper III rankHelper 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, 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 1variant 2Year
1112017
222018
52.22019
61.82020
97.22021
3232022
20.52023
67222024
842025

 

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

5 Replies

  • BITomS's avatar
    BITomS
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

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

      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's avatar
        BITomS
        Icon for Solution Supplier rankSolution 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())