cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

build 12 month budget table using last month sales

In summary, I am trying to create a forward looking forecast using the last full month of sales. Everything I researched has Sales vs Budget tables, in my case I have no Budget [Monthly] table, because I want to use last month sales (full month) as my full year "budget" or Run Rate by month, so I can apply the below-link and get a Forecast. New month rolls off, looks at Sales (actual) and remaining months of the year looks at Budget/Run Rate.

 

https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/

 

My model is standard tabular, Sales, Calendar, etc.  I decided to not share [measure] details unless you need them, as I am sure someone has written DAX on this SPECIFIC request, I just have not found the solution yet.

4 REPLIES 4
Super User III
Super User III

A while back I implemented a slightly different variation. ignoring the current month (as it may not have complete data) I used a sliding window (say, 11 months look back) to compute and remember the forecast for the month following the slider. Then that computed forecast would be part of the next sliding window , akin to the snake eating its tail, and so on.  This would take seasonality into account, and eventually peter out gracefully.

Thanks for reply.  We have a budget set, however this is more peer -performance numbers I'm after. I have a +/- slider, so they can see at current rate +/- individual target. I just can't figure out how to project forward months 2021 the last month run rate. As actual months close the future month become less voltile, and will be able to see their targets which they use the % +/- slicer so I dont have to exposte publish them.

Super User III
Super User III

warning: basing a projection on a single data point is extremely risky, especially for data that is subject to lots of seasonality.

Yes, I understand this concept. The sales are recurring monthly; usually for a 12 month term, therefore less volatile as you might imagine. I am ok, with last 3 months average, how to do forecast the 3month average for the rest of the year? 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors