In this blog, I talk about a technique around Power BI budgeting, working out a way to allocate calculations across different time frames or months, for example. You may watch the full video of this tutorial at the bottom of this blog.
In most cases, budgets are in a yearly basis. However, it’s so much better to narrow it down to a monthly basis and see the allocation of our budgets per month. So I’m going to show you, through a demonstration, a few nuances of how this all works. We’ll dive into some advanced DAX calculations as well.
The data that I use in this tutorial was part of an Enterprise DNA Learning Summit around budgeting sensitivity tables.
Let’s have a look at the scenario and the data model, which is really key in understanding how to achieve this budgeting analysis.
The Budget Sensitivity Table
So in this scenario, we have some budgets per city for 2018 and they’re in a yearly time frame.
We can’t just have one number for the entire year, we need to allocate these budgets at a monthly level. We can even allocate this to a daily level, which I have explained in other video tutorials. This way, we’ll be able to see what the trend is and how things perform through time.
To do this allocation, we identify the Budget Sensitivity for each month, and integrate it into our calculations. In this table, the Budget Sensitivity is breaking down the budget by each month.
Sensitivity or seasonality in Power BI could be a variety of things, but in this analysis, it’s based on our budgets or forecasts. And so here we forecast that we have an increase of allocation required in the middle of the year versus summertime, for instance. There’s also a slight increase during Christmas.
Then, we need to find a way to integrate this into our analysis. Before we get to that, let’s look at how the data model is structured.
Data Model Set Up
This is a very detailed model with all our lookup tables, fact tables, key measure, and supporting tables for a detailed Power BI budgeting analysis.
If we look closely, we can see that our Regional Budget table doesn’t have any relationships with other tables.
Likewise, our Budget Sensitivity table.
These two tables are what I call supporting tables. They don’t have any physical relationship with other tables in the model. This Budget Sensitivity table is going to support the logic that we’re going to complete in our model.
DAX Calculation To Allocate Budget Monthly
First, let’s just have a quick look at our Yearly Budget calculation.
If we drag it into our table, we can see that it’s not allocated at all. It’s only showing the full number. We want to allocate this budget across each month.
To do that, we create a new measure. We’ll call it 2018 Budget Allocation.
Remember that the Budget Sensitivity table has no relationship to the Date table in the model, so there’s no way for us to grab information from the Budget Sensitivity table and have it filtered from the Date table.
But, we created that connection virtually inside this formula using the TREATAS function.
Inside the Budget Sensitivity table, we have our Month Name column. And if we go to our Date table, we have a column that is very similar. It’s a calculated column that’s showcasing the first three letters of each month.
And so even though we can’t draw a physical relationship between these two tables (Budget Sensitivity and Date tables), we can do it virtually. This is where the real power of this budget allocation technique comes in.
By using TREATAS, we created that connection between the Date table Short Month column and the Budgets Sensitivity table Month Named column. And that’s how we get a breakdown of the correct allocations for the correct months.
We then integrate this calculation into other formulas to get our monthly allocation.
In this formula, we use it as a variable (VAR). Then, we go ISFILTERED Month & Year (in the Dates table), and multiple BudgetAllocation by Yearly Budget. If not, return to the Yearly Budget.
And so we can see now we have this monthly allocation via the Sensitivity table using just some DAX formulas.
***** Related Links *****
Allocate Monthly Forecasts Across Daily Results In Power BI Using DAX
Managing Seasonality In Your Budget Analytics – Advanced Power BI
How To Create Budgets Which Have Seasonality Adjustments – Power BI Technique
Conclusion
In this tutorial, I have shown you how to utilize supporting tables and DAX formulas, particularly with TREATAS, to create a virtual relationship and allocate monthly Budgeting Sensitivity.
This Power BI budgeting technique is a brilliant one that has so many applications. This methodology will clean up your model immensely as well. It simplifies things and generates insights that were so difficult to do before.
I hope you can get this concept and integrate it into your own budgeting and forecasting reports somehow.
Cheers!
Sam
