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).

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.

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.

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.

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!

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)

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. 3. 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. 4. 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. 5. 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). 6. 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).

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!