cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## DAX Calculation for Year over Year Analysis

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 =
RETURN
DIVIDE(([Expense]-PreviousYearExpense),PreviousYearExpense)
2 REPLIES 2
Super User

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)
Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors