Forum Discussion

dokat's avatar
dokat
Post Prodigy
4 years ago
Solved

Dynamically calculate YoY changes based on date selection on slicer

Hi,   I have a large data set i am trying to do year over year calculations based on a date selection in a slicer. Below is sample datatset.   I have a slicer based on calendar year column and th...
Need Help
  • ALLUREAN's avatar
    ALLUREAN
    4 years ago

    Hi, dokat 

    Try this:

     

    YoY Variance Measure =
    VAR __PREV_YEAR = CALCULATE(SUM('P&L'[Values]), DATEADD('Date'[Date], -1, YEAR)

    //Replace 'Date'[Date] by your date column coming from Calendar table

    VAR __CURR_YEAR = SUM('P&L'[Values]) --Selected by slicer
    RETURN
    __CURR_YEAR - __PREV_YEAR

     

     Or percent change:

     

    YoY% Measure =
    VAR __PREV_YEAR = CALCULATE(SUM('P&L'[Values]), DATEADD('Date'[Date], -1, YEAR)

    //Replace 'Date'[Date] by your date column coming from Calendar table

    VAR __CURR_YEAR = SUM('P&L'[Values]) --Selected by slicer
    RETURN
    DIVIDE(__CURR_YEAR - __PREV_YEAR, __PREV_YEAR)

ALLUREAN's avatar
ALLUREAN
Solution Sage
4 years ago

Hi, dokat 

Try this:

 

YoY Variance Measure =
VAR __PREV_YEAR = CALCULATE(SUM('P&L'[Values]), DATEADD('Date'[Date], -1, YEAR)

//Replace 'Date'[Date] by your date column coming from Calendar table

VAR __CURR_YEAR = SUM('P&L'[Values]) --Selected by slicer
RETURN
__CURR_YEAR - __PREV_YEAR

 

 Or percent change:

 

YoY% Measure =
VAR __PREV_YEAR = CALCULATE(SUM('P&L'[Values]), DATEADD('Date'[Date], -1, YEAR)

//Replace 'Date'[Date] by your date column coming from Calendar table

VAR __CURR_YEAR = SUM('P&L'[Values]) --Selected by slicer
RETURN
DIVIDE(__CURR_YEAR - __PREV_YEAR, __PREV_YEAR)

dokat's avatar
dokat
Post Prodigy
4 years ago

made modification to my data table and this worked.