topic Re: Question Regarding Dateadd function in DAX Commands and Tips

Question Regarding Dateadd function

Anonymous — Mon, 13 Feb 2023 14:45:37 GMT

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)

Re: Question Regarding Dateadd function

ValtteriN — Mon, 13 Feb 2023 15:22:25 GMT

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/

Re: Question Regarding Dateadd function

Anonymous — Mon, 13 Feb 2023 16:13:58 GMT

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.

Re: Question Regarding Dateadd function

Anonymous — Mon, 13 Feb 2023 16:18:36 GMT

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