Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
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 =
    CALCULATE([Expense], DATEADD('Calendar'[Date],-1,YEAR))
RETURN
    DIVIDE(([Expense]-PreviousYearExpense),PreviousYearExpense)
2 REPLIES 2
FreemanZ
Super User
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?

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors