Creating Budgets With Seasonality Adjustments Automatically - Power BI Technique


#1

#2

Hi Sam … this technique is working really well for me, but would you mind giving me a quick pointer as to how to adjust the central formula to take an average of the last 3 years worth of Sales? I’d like to lower the likelihood of an outlier in the last year distorting the budget for this year. Thanks so much!
Peter


#3

Yep sure thing.

I actually covered a similar technique in a recent live workshop

Check here for around the 23 min mark

In terms of that specific formula I think this should help here

Product Budgets = 
ADDCOLUMNS(
    CROSSJOIN( SUMMARIZECOLUMNS( Sales[Product ID] ), DISTINCT( Dates[MonthName] ) ),
        "2018 Budgets", AVERAGEX( FILTER( VALUES( Dates[Year] ), Dates[Year] >= 2015 ), [Total Sales] )* 1.075 )

Give this idea a go and let me know how it turns out.

If this specific example the data only goes back to 2017 so it doesn’t make any changes, but see if this adjusts your own results.


#4

Hi Sam,

I’m afraid to say I’ve got myself confused between the 2 methods!

I’ve been able to replicate the 3yr average from the ‘predicting future outcomes’ workshop, but then can’t filter it by the budget categories as it hasn’t been passed through the regional budget table. And I can’t work out how to integrate the future outcome work into the regional budget table from the ‘seasonality’ technique.

Also, where should the specific formula that you provided (Product Budgets) be inserted into the method?

Sorry!
Peter


#5

Is it possible to share more info around the scenario? Potentially add a pbix file…or more detailed images of what you’re looking at.

I suspect the data model is where this need to be solved here.


#6

I’ve replicated my issue into your pbix (attached). I’ve added custom fiscal years to the date table as I’ll need to view this that way and wanted to be certain that wasn’t contributing to the problem.
Predicting Future Results v1.pbix (677.4 KB)

My first issue is that I can’t get the 2 methods to show the same amount for FY19 … I would expect ‘Budgeting Measures’[Average Sales] and ‘Regional Product Budgets’[FY19 Budgets to show the same total?

Because I can’t get the same total, I then can’t figure out how to replicate the method you used that passes the Sales through the ‘Regional Product Budgets’ table so that the results can be filtered by location (or product).

The overall aim is the FY’19 budget, based on prior 3 yr sales, adjustable by a budget factor (currently 1 in this sheet), and then filterable based on groups/criteria.

Am I making any sense?!


#7

Cool, you’re honestly not far away.

Nice work on the 3 years formula, but now you also need to integrate it in you model like the below.

There’s a few differences between these formulas.

I think we need to simplify this here. I can understand there’s a bit going on.

First the seasonality table actually equals 108% in total (which was done purposely to include the impact of growth from the base forecast)

image

While you’re formula here doesn’t take this into account.

Regional Product Budgets = 
ADDCOLUMNS(
    CROSSJOIN( SUMMARIZECOLUMNS( Sales[Product ID], Sales[Location ID]), DISTINCT( Dates[Fiscal Month] ) ), 
        "FY19 Budgets", AVERAGEX( FILTER( VALUES( Dates[Fiscal Year] ), Dates[Fiscal Year] >=2015 && Dates[Fiscal Year] <2019 ), [Total Sales]) * 1)

So that’s the first difference.

Also the seasonality percent is just made up numbers also (this was for demo purposes), it doesn’t relate to historically trends.

Ok I’m thinking to simplify this.

Check out the below - this is for the budget table

Regional Product Budgets = 
VAR BudgetFactor = 1

RETURN
ADDCOLUMNS(
    SUMMARIZE( Sales, Products[Product ID] , Locations[Location ID], Dates[MonthName] ),
    "2019 Budget", AVERAGEX( FILTER( ALL( Dates[Fiscal Year] ), Dates[Fiscal Year] > 2015 && Dates[Fiscal Year] <= 2017 ), [Total Sales] * BudgetFactor ) )

image

So with this one formula you have an average budget per month for each product and location combination

Then if you want to filter this by anything in the date table then you need to create the correct DAX formula using the TREATAS function to create the virtual relationship.

Total Budgets = 
CALCULATE( SUM( 'Regional Product Budgets'[2019 Budget]),
    TREATAS( VALUES( 'Regional Product Budgets'[MonthName] ), Dates[MonthName] ) )

I’m not too sure why you need all the additional fiscal columns as if you just change the columns here, you can just use the standard month column in your visuals.

Ok see how you go with this.

Attached
Predicting Future Results v1.pbix (630.7 KB)

My recommendation is probably forget about the sensitivity for now and try to just setup you budgeting table correct and make sure it’s integrated into your core model.


#8

Thanks so much for taking the time to look at this, and for being so patient and encouraging!

With the sensitivity stripped out and the BudgetFactor set at 1, shouldn’t the Total 2019 Budget equal the Average Sales from the last 3 years? At the moment it’s showing approx 3x but not exactly 3x. I haven’t made that many changes to the attached apart from to include a couple of table visuals to try and demonstrate where I’m seeing differences in the final numbers.
Predicting Future Results v2.pbix (637.8 KB)

(I included the extra Fiscal columns so that the formulas more closely matched my model when I don’t have the calendar columns; our fiscal year starts on 1st July and so the Fiscal Month column is ordered to set month 1 to July.)


#9

Sorry on this,

It’s certainly not right, so apologies for not getting the right result to you yet.

I’m even confusing myself a little bit on this which is annoying.

This table is the problem

So I’m trying to simplify everything and break it down piece by piece.

I’m trying to isolate the calculation by itself in a calculated column

This is the problem so I’m focused on this.

Will have to pick up on it tomorrow


#10

There’s actually a lot to this, when you’re attempting to filter virtual by multiple columns like this. Getting the context right can be quite difficult.

This gets pretty close, but currently doesn’t have the product filter

Regional Product Budgets =
ADDCOLUMNS(
      CROSSJOIN( VALUES( Dates[MonthName] ), VALUES( Locations[Location ID] ) ) ,
      "Budgets", DIVIDE( CALCULATE( [Total Sales], Dates[Year] &gt; 2015 &amp;&amp; Dates[Year] &lt;= 2018 ), 3, 0 ) )

Played around a bit more and I think this does it actually

Regional Product Budgets = 
ADDCOLUMNS(
    GENERATE(
        CROSSJOIN( VALUES( Dates[MonthName] ), VALUES( Locations[Location ID] ) ),
            VALUES( Products[Product ID] ) ) ,
                "Budgets", DIVIDE( CALCULATE( [Total Sales], Dates[Year] > 2015 && Dates[Year] <= 2018 ), 3, 0 ) )

I myself am going to need to investigate this a bit more.

It’s an interesting one.

Then I used this measure

Total Budgets = 
CALCULATE( SUM( 'Regional Product Budgets'[Budgets] ),
    TREATAS( VALUES( Dates[MonthName] ), 'Regional Product Budgets'[MonthName] ) )

To get this

image

See if this works are your end.


#11

Well it’s really reassuring that even you’re finding it difficult to do!

That does seem to have worked. I can now use the budget factor to adjust to the pre-set annual budget and filter it according to the criteria that I need to look at.

I noticed that the TREATAS criteria have swapped round in the Total Budget measure … does that make a difference to the answer?

Thank you so much for finding a solution to this. I now have a pretty powerful view to be able to demonstrate how we’re tracking to both Sales and Orders budgets. Really really appreciate the help.


#12

That’s great, glad we got there.