Nostradamus :: A seasonal cost forecast in DAX for the FinOps Toolkit

You don’t finish a forecast, you abandon it.

 

Sometimes I am proud of what I do.

Only sometimes? OK, I admit it, I have a sort of egocentric character.

OK, so what are we talking about today? We are gonna talk about the fact that I created a valid forecast that you can plug into your…  FinOps Toolkit. In fact the FinOps hub ADX dashboard has one, built on series_decompose_forecast, but it lives in KQL and it doesn’t care about your Power BI slicers.

Why should I care about slicers? Because finance never asks “how much will Azure cost next quarter?”. They ask “how much will Production cost?”, “how much will that team cost?”, “how much will that application cost if nothing changes?”. A forecast that can’t follow a slicer answers a question nobody asked.

FinOps Toolkit - Forecast

How did it all start?

I was browsing GitHub and I found Frank Contrepois’ finops-toolkit for AWS. Different cloud, same name, and a beautiful collection of forecasting methods: moving averages, exponential smoothing, ARIMA, Prophet… all in Python.

So you installed Python in Power BI? God, no. I already had my Azure costs in Power BI through the FinOps hub. I didn’t want a pipeline, a notebook, or a Python runtime. I wanted the logic, natively, as DAX measures.

I started with a simple moving average and exponential smoothing. It was a good start. Then I threw it away.

Why? Because cloud costs are not a stock price. They have a rhythm: weekends are cheaper than weekdays, some months are busier than others, and every company has its own seasons. A moving average flattens all of that into a nice, smooth, useless line.

Which model should I use, then? There are dozens, and every one of them has a fan club. Here you can find a good overview of the top 7 forecasting models, with a table to choose the right one.

Last year’s version was a mess

Last year we were using a mix: a moving average plus the previous two years, each with its own weight. A bit of moving average here, a pinch of last year there, a spoonful of the year before. The chart looked fantastic.

So what was the problem? It was a mess. Two problems in particular:

  1. I used EDATE(-12) to go back one year. One calendar year is 365 days, which is 52 weeks and 1 day. So every Saturday of last year landed on a Friday of this year, and the weekend dips appeared on the wrong days.
  2. It never really looked at this year. It was busy weighing the previous two years against each other, while this year was running well below last year. The forecast went up while reality went down.

A forecast that looks right is the most dangerous thing in a FinOps report. People believe it.

And the new one is not a mess? Oh, it is. But it’s a controlled mess. A controlled mess in a satanic mass. And it may still contain moss. I’m fine with that. Moss is low maintenance.

So how does it work now?

In one sentence, the one I use with finance:

Next year’s day = the same weekday last year × how much faster or slower we are spending now.

That’s it. Two ingredients:

  • The shape. For each future day I go back 364 days (exactly 52 weeks, so a Monday stays a Monday) and I take the average of that day, the week before and the week after. Three points. This keeps the weekly rhythm and the yearly seasonality, and it stops a one-off charge from last year becoming a permanent spike.
  • The level. The last 28 days divided by the same 28 days 52 weeks earlier. If you are spending 10% less than last year, the whole shape goes down by 10%.

And for dates more than one year ahead? The day 364 days back is still in the future, so I go back 728 days (104 weeks) and apply the level twice.

And if I have less than one year of history? Then there is no “same weekday last year” to copy, and the forecast falls back to the average of the last 28 days. Boring, but it doesn’t leave you with an empty chart.

OK, show me the code

First, a calendar that goes until the end of next year. The FinOps Toolkit KQL dataset doesn’t have one, so you need to create it as a calculated table:

Relate Costs[x_ReportingDate] to 'Forecast Calendar'[Date] and set the Default Granularity parameter to Daily. The shape needs days, not months.

What about the currency switch? Not today. In my own report the forecast sits on top of a currency switch, but that deserves its own article, and it will be the next one. Here everything starts from one measure. Change it once and the whole forecast follows:

Data Lag Days is there because the latest day in the hub is usually incomplete. If the last days of your chart look like a cliff, start from here.

The level:

And the star of the show:

Plus three small helpers for the visuals:

 

Why Forecast and Forecast Only? Charts use Forecast: it repeats the last actual day so the actual line and the forecast line touch. Tables and totals use Forecast Only, otherwise that bridge day is counted twice. Yes, I learned it the hard way.

Where are the years? 2024, 2025, 2026…? There are none. Put ‘Forecast Calendar'[Year] on the columns of a matrix with Total with Forecast and the years appear by themselves. Next January the report moves forward on its own. Nobody has to remember to change a measure called “2026”.

DAX traps I fell into (so you don’t have to)

  • Never name a variable like a DAX function. VAR LastDate collides with LASTDATE() and gives you a red squiggle that makes no sense. Prefix every variable with _ and sleep well.
  • Never call a measure inline inside the filter of another CALCULATE on the same table. You get “A function ‘PLACEHOLDER’ has been used in a True/False expression…”. Who the hell is PLACEHOLDER? Nobody knows. I like to think he’s somebody’s disappointing ex. Compute the measure in a VAR first, then use the variable.
  • MAX(Date) is not your friend at month level. My year-over-year comparison took the last day of the month and returned one day of cost against a whole month of actuals. About 30 times too small, and it looked perfectly plausible. Always iterate with SUMX ( VALUES ( Date ), … ).
  • A zero weight still costs. A VAR you multiply by zero is still computed. If a piece of the formula is not used, delete it.

Naaa… it cannot be that good

You are right to be suspicious. I was too. So I ran a backtest on a real FinOps hub: 24 times, I pretended that “today” was a month-end between July 2024 and June 2026, forecasted the next 3 months, and compared the result with what actually happened.

Against two opponents: a plain run-rate (the average of the last 30 days, the method everyone uses in Excel without admitting it) and the series_decompose_forecast from the hub dashboard.

Horizon My forecast Run-rate (last 30 days) Hub dashboard
Month 1 6.8% 6.3% 6.9%
Month 2 9.8% 10.6% 11.9%
Month 3 13.1% 15.2% 19.9%
3-month total 9.2% 9.9% 11.8%

 

Average absolute error of the forecast total. Lower is better.

So you win? Mostly. Let me be honest, because this is a blog between friends:

  • For next month, the run-rate is slightly better. If you only care about the next 30 days, the average of the last 30 days is hard to beat.
  • The further you look, the more the seasonality pays off. At 3 months the dashboard method is off by almost 20%. Mine by 13%.
  • It shines at seasonal turns. Forecasting from the end of March 2025, it was off by +5.5%. The run-rate by +28.1%. The dashboard by +33.8%.
  • It suffers when the level changes suddenly. When costs dropped hard at the end of 2025, it kept copying last year’s higher months and over-forecasted by up to 22% for a while, until the 28-day level caught up.

Why are you telling me the bad part? Because a forecast you don’t understand is worse than no forecast. Now you know when to trust it and when to look twice. So you will have all the answers when the CFO comes to your desk with a stick in hand.

Does it work if my costs have no seasonality?

Yes. If your costs are flat, last year’s shape is flat too, and the level does the job. It behaves like a run-rate that also knows that weekends are cheaper. If your costs grow, the 28-day level follows the trend. If you have less than one year of data, it falls back to the run-rate.

So it adapts to everyone? That’s what I believe. But I tested it on one hub. Mine. And I don’t trust anybody who tests on one dataset and claims it works everywhere. Including me.

Now it’s your turn

I proposed it to the FinOps Toolkit team in this GitHub issue. In the issue there is the backtest as a KQL query: paste it into the Hub database of your FinOps hub, run it (it takes about a second), and you get the same table as above, with your numbers.

Then what? Paste your result in the issue. If the method beats the run-rate on your data too, we have a case for putting it in the toolkit for everybody. If it doesn’t, I want to know that as well.

There is also a standalone KQL version of the forecast in the comments, ready for a dashboard tile, if Power BI is not your thing.

What’s next? The currency switch. Same report, same toolkit, one slicer to flip between currencies, and a couple of surprises I found on the way. Stay tuned.

Why no one did this before? I don’t know. Just re-post this and share the love. 🔮

Have you used an AI to do this? Of course, this wouldn’t have been possible without Claude Code. And guess what, I’ve used Claude to write this blog post too. This is my first blog post written by an AI. You are an AI.
Me? ….oh my….

FinOps Toolkit - Luca Ponsato
Luca Ponsato :: https://www.instagram.com/lucaponsatoart

Related

Leave a Reply

Your email address will not be published. Required fields are marked *