Forum Discussion

BibiC_2024's avatar
BibiC_2024
New Member
2 years ago
Solved

YOY $ and % variance from filtered values

Hi, can you help me with the formula to calculate variances based on filtered values?

I have this formula to calculate the variance with previous year, but how can I change it to be based on the current year vs filtered year?  Thanks in advance for your help!

 

YOY Var $ =
VAR __PREV_YEAR =
    CALCULATE(
        [Actual Short Value],
        DATEADD('Calendar_Ultimate'[Date], -1, YEAR)
    )
RETURN
    [Actual Short Value] - __PREV_YEAR
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Try the following : 

    YOY Var $ =
VAR CurrentYearValue = 
    CALCULATE(
        [Actual Short Value],
        FILTER(
            'Calendar_Ultimate',
            'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year])
        )
    )
VAR FilteredYearValue = 
    CALCULATE(
        [Actual Short Value],
        FILTER(
            'Calendar_Ultimate',
            'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year]) - N // N represents the number of years back you want to filter
        )
    )
RETURN
    CurrentYearValue - FilteredYearValue

     

    And for the percentage variance :

    YOY Var % =
    VAR CurrentYearValue = 
    CALCULATE(
    [Actual Short Value],
    FILTER(
    'Calendar_Ultimate',
    'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year])
    )
    )
    VAR FilteredYearValue = 
    CALCULATE(
    [Actual Short Value],
    FILTER(
    'Calendar_Ultimate',
    'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year]) - N // N represents the number of years back you want to filter
    )
    )
    RETURN
    DIVIDE(CurrentYearValue - FilteredYearValue, FilteredYearValue)

      

     

     

     

1 Reply

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    2 years ago

    Try the following : 

    YOY Var $ =
VAR CurrentYearValue = 
    CALCULATE(
        [Actual Short Value],
        FILTER(
            'Calendar_Ultimate',
            'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year])
        )
    )
VAR FilteredYearValue = 
    CALCULATE(
        [Actual Short Value],
        FILTER(
            'Calendar_Ultimate',
            'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year]) - N // N represents the number of years back you want to filter
        )
    )
RETURN
    CurrentYearValue - FilteredYearValue

     

    And for the percentage variance :

    YOY Var % =
    VAR CurrentYearValue = 
    CALCULATE(
    [Actual Short Value],
    FILTER(
    'Calendar_Ultimate',
    'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year])
    )
    )
    VAR FilteredYearValue = 
    CALCULATE(
    [Actual Short Value],
    FILTER(
    'Calendar_Ultimate',
    'Calendar_Ultimate'[Year] = SELECTEDVALUE('Calendar_Ultimate'[Year]) - N // N represents the number of years back you want to filter
    )
    )
    RETURN
    DIVIDE(CurrentYearValue - FilteredYearValue, FilteredYearValue)