DAX Help: Trying to calculate YoY change and the predict future costs

[millerfdan]

Hello,

I’m looking for some help with a DAX issue that I just can’t figure out. This is for a school project, and I’ve been stuck on it for hours.

I’m trying to calculate the Year-over-Year (YoY) growth for different road work costs across various counties (and statewide) in Iowa. Specifically, I want to create a measure that works with the two slicers shown in the attached screenshot, calculating the YoY % change from the previous year (e.g., 2011 compared to 2010, and so on).

After that, I plan to calculate the average yearly change based on that measure to make a prediction for future years (2024, 2025, and beyond)—but I’ll tackle that part later.

The issue is that I cannot get the first measure to work. Everything I’ve tried either:

1. Returns a random number in the total row and nothing in the individual rows.
2. Results in a blank column in the table.

I’ve tried using ChatGPT, but it keeps regurgitating the same failing solutions. I’m not sure if the issue is that each year appears multiple times or something else. I even unpivoted the cost columns (Granular Cost, Paved Cost, etc.), but that didn’t resolve it either.

If anyone can help, I would really, really appreciate it. Thanks in advance!

I have attached the dataset and a table showing the two slicers im working with.

Thank you.

[Anonymous]

Hi, @millerfdan 

Can you provide example files and the output you expect? This will better help you solve the problem. Please remove any sensitive data in advance.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is to create a star schema data model, like below, and try to use OFFSET DAX function in the measure.

OFFSET function (DAX) - DAX | Microsoft Learn

Granular Cost measure: = 
SUM( cost[granular_cost] )

OFFSET function (DAX) - DAX | Microsoft Learn

Previous year: = 
CALCULATE (
    [Granular Cost measure:],
    OFFSET ( -1, ALL ( 'year'[year] ), ORDERBY ( 'year'[year], ASC ) )
)

YoY %: = 
DIVIDE( [Granular Cost measure:] - [Previous year:], [Previous year:] )

[millerfdan]

This works, thank you for the help!