Question Regarding Dateadd function

[Anonymous]

So I currently have a formula that determines the year over year % change. However, in my dataset, the following years are used:

2019, 2021, 2022, 2023. The formula works perfectly for all the years except the 2019-2021 change. How could I modify the below formula to calculate the change from 2019-2021 instead of 2020-2021. In other words, how can I make the DATEADD function use -2 if there is no data for a year. 

YoY Expense =

VAR PreviousYearExpense =

    CALCULATE([Expense], DATEADD('Calendar'[Date],-1,YEAR))

RETURN

    DIVIDE(([Expense]-PreviousYearExpense),PreviousYearExpense)

[ValtteriN]

Hi,

Here is one way to do this:

Measure 42 =

VAR PreviousYearExpense =

    CALCULATE(SUM('Table (28)'[value]), DATEADD('Calendar'[Date],-1,YEAR))

VAR PreviousYearExpense2 =

    CALCULATE(SUM('Table (28)'[value]), DATEADD('Calendar'[Date],-2,YEAR))    

var _yoy1year = DIVIDE(SUM('Table (28)'[value])-PreviousYearExpense, PreviousYearExpense)

var _yoy1and2 = IF(ISBLANK(_yoy1year),DIVIDE(SUM('Table (28)'[value])-PreviousYearExpense2, PreviousYearExpense2))

RETURN

    _yoy1and2


Data:
End result:

Since 2022 didn't have data we calculate 2021 values. So 2023 - 2021 / 2021 
2023 values = 1750
2021 values = 500

(1750-500) /  500 = 2.5 

End result is 2.5. as expected.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

This is what I did

YoY Expense =

VAR PreviousYearExpense =

    CALCULATE(SUM('Table'[Expense]),DATEADD('Calendar'[Date],-1,YEAR))

VAR PreviousYearExpense2 =

    CALCULATE(SUM('Table'[Expense]),DATEADD('Calendar'[Date],-2,YEAR))

VAR YoY1 = DIVIDE(SUM('Table'[Expense])-PreviousYearExpense,PreviousYearExpense)

VAR YoY2 = IF(ISBLANK(YoY1),DIVIDE(SUM('Table'[Expense])-PreviousYearExpense2,PreviousYearExpense2))

RETURN

    YoY2

[Anonymous]

Hi,

I really appreciate you helping me out! So I used your formula and it shows the correct YoY for 2019-2021 but it now shows a blank for all of my other years. When I try to look at 2022-2023 change, it just displays a blank.