topic DAX Calculation for Year over Year Analysis in DAX Commands and Tips

DAX Calculation for Year over Year Analysis

Anonymous — Thu, 02 Feb 2023 20:03:02 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 DAX ignore a year if there is no data for that year. Below is the formula I am using. Thanks in advance!

YoY Expense =

VAR PreviousYearExpense =

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

RETURN

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

Re: DAX Calculation for Year over Year Analysis

FreemanZ — Thu, 02 Feb 2023 20:42:00 GMT

hi Anonymous

try like:

YoY Expense =

VAR CurrentYear =

SELECTEDVALUE('Calendar'[Year])

VAR PreviousYear =

MAXX(

FILTER(

ALL(FactTable),

YEAR(FactTable[Date])<CurrentYear

YEAR(FactTable[Date])

)

VAR PreviousYearExpense =

CALCULATE(

[Expense],

'Calendar'[Year] = PreviousYear)

)

RETURN

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

Re: DAX Calculation for Year over Year Analysis

Anonymous — Fri, 03 Feb 2023 14:02:58 GMT

Sorry if this is a dumb question, but what is a FactTable and how do I create one?