How to split budgets to lower granularity with DAX (PowerBI)

When creating a budgeting prediction, most of the time we are creating it on the higher granularities. Rarely we do it on a daily basis.

But sometimes we are required to match budgets to actual sales on lower granularities than the one we previously set (e.q. the manager wishes to see how would the budget split look like if we were to split it on a daily basis to see which days are above average).

Budget split to lower granularities

In those scenarios, we have 2 options (if we do not want to manually split budget granularity, which would be a tedious job).

  • Split the granularity linearly by the number of days in observed periods,
  • Split the granularity based on the actual ratios (we take the previous year sales ratio and apply it to this year budget).

In this post, we will be dealing with the first option, while in the next post we will examine how to perform budget split based on ratios and what to be aware of while implementing calculation.

 

The model

To start with, we will use the Sales and Marketing sample database from Microsoft. If you wish to follow along, you can download the .PBIX file here.

The data model is a simple one, consisting of 3 dimensions, fact_sales table and a TargetBudget table which is set on a quarterly granularity.

Data model


The easiest way to connect a Dim_date table to TargetBudget table, even though they are not on the same granularity (Dim_date is daily while TargetBudget is quarterly), is to put all sales in the TargetBudget table to the first date of that quarter, like in the picture below.

For every product, full quarter sales are put on the first date of the respective quarter.


This way we can easily connect the two tables with a 1 to * relationship, and calculations will produce correct results on the yearly/quarterly level. The issue becomes visible when we decide to drill down to lower levels. As we can see in the picture below, when we drill to month, the budget revenue stays at Quarterly level, which makes it hard to compare.

Although we drilled down to monthly granularity, TargetBudget sales stayed at the quarter granularity.


What we would like to see is a linear split of the budget values to monthly level (or any other lower granularity), as represented with the red line!

Red line represents the split of quarter sales to monthly level.

To accomplish linear split on lower granularities, we need to create a dynamic calculation that will correctly divide quarterly sales into any other lower granularity, while maintaining the correct summed results on all the levels above quarter.

In the rest of the post, we will go through the formula that accomplishes this task and explain its steps. You will also get a clearer view of how to use it in your own models.

The logic behind the formula

If we want the formula to produce a correct result on every granularity, we need to set the TargetBudget granularity to Fact_sales one, which is daily. After that, we need to sum up individual daily sales with the respect of the current filter context (or in simpler terms, based on the currently used granularity of the visual).

We also have to take into consideration that the formula needs to split budget values only when drilled down to lower granularity than Quarterly, while on the higher granularities it needs to provide correctly summed quarter values.

Formula

BudgetSplit =
VAR FirstQTRDate =
    STARTOFQUARTER ( Dim_date[Date] )
VAR LastQTRDate =
    ENDOFQUARTER ( Dim_date[Date] )
VAR BudgetDates =
    DATESBETWEEN ( Dim_date[Date], FirstQTRDate, LastQTRDate )
VAR BudgetToDivide =
    CALCULATE ( [TotalBudget], Dim_date[Date] IN BudgetDates )
VAR NumOfDays =
    COUNTROWS ( BudgetDates )
VAR DailyAmount =
    DIVIDE ( BudgetToDivide, NumOfDays )
RETURN
    SUMX ( VALUES ( Dim_date[Date] ), DailyAmount )


Explanation:

  1. We set the starting and ending points of the quarters (FirstQTRDate and LastQTRDate)
    1. This will give us the correct start and end of quarter no matter which granularity we use. E.q. if we use yearly granularity, the quarter start will be 1/1/2019 (first day of the 1st quarter), while the quarter end will be 12/31/2019 (last day of the 4th quarter).
    2. On quarter and lower granularities starting and ending points will always be the start/end date of the quarter under a given filter context.
  2. BudgetDates variable – We need to determine which days are included inside our quarter borders – we will use this variable in 2 different places and with 2 different outcomes.
  3. BudgetToDivide variable – We use this variable to determine the total budget that needs to be split, taken into consideration different granularities. In the CALCULATE argument part, we must use the IN syntax since the BudgetDates variable will always return a table of values. On the yearly granularity, it will return the sum of all quarters, while on quarter and lower granularities, it will always return a quarterly budget.
  4. NumOfDays variable – Counts the number of days at the Target granularity (the number will never go below 90 if the granularity is drilled below a quarter, but will also return yearly number (365) in case we use yearly granularity).
  5. DailyAmount variable – Splits the BudgetToDivide amounts to average daily amounts.

After we acquire the DailyAmount figure (no matter which granularity is used in the visual), we create an iteration over the date’s column, summing the DailyAmount with the respect of the current filter context.

On the yearly granularity, The formula will divide total yearly sales by 365 to get a DailyAmount, and then iterate through 365 days summing the same DailyAmounts. This way, for the higher granularities (above quarter), we will get identical figures as if we were to sum the total quarter sales.

The lower granularities (below the quarter one) is where the formula shines. On the lower granularities (EQ weekly), for a given week, we will again get a DailyAmount (this time by dividing quarterly budget with 90, check 4th step in the explanation) and then iterate through 7 days summing the DailyAmounts to get the total weekly amount. If we were to sum all the weekly figures that fall into one quarter, we will get the total quarterly target Amount.

The calculation provides an accurate and correct split of target data no matter which date hierarchy we use (Yearly, Quarterly, Monthy, Daily). It also provides accurate split in case of a non-standard split (e.q. weekly).

The budget values are correctly split no matter which granularity we use

In case you need an accurate linear split of higher date granularities, then this could be the way to go. If you need a more detailed granularity split (e.q. by the LY historical ratios) the formula becomes a bit more complex. I will try to explain this option in the following post.

Hope you find this date granularity split technique helpful!

Feel free to comment in the comment section below!

Table of Content

Table of Content

LEARN POWER QUERY

Improve your PowerQuery skills with
Exceed Academy

COMMENTS

Subscribe
Notify of
guest
12 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
12
0
Would love your thoughts, please comment.x
()
x
Scroll to Top