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

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

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.



  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


Improve your PowerQuery skills with
Exceed Academy


Notify of
Oldest Most Voted
Inline Feedbacks
View all comments


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

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

Dynamic Coalesce in PowerQuery

We can define coalesce in PowerQuery as the last value existing in a single row of multiple columns positioned from left to right. In our example that would be the last value from the month columns. In this article, we will show you how to create both static and dynamic...

Read more

Dynamic bulk insert to Power Apps collection

In one of our latest blog posts, we described a technique used to copy tables from other programs (e.g. Excel) into PowerApps collections. This solution had a drawback. Within the code, the column order was referenced to determine which column to collect. This means that, for the first column in...

Read more

Bulk insert to Power Apps collection

In this blog, we will show you the technique used to collect tables for your app by using the bulk insert. We will show you how to make the same table structure in Power Apps as Excel tables. We will use Excel to show you how to copy-paste tabular data...

Read more

Why should you start using Power Apps?

In this article, we will take a look at what is Power Apps, why should we use it, and when organizations can benefit from custom apps created in the Power Apps platform.Many organizations are not yet familiarized with Power Apps capabilities, and the ways it can improve their agility, modernize and automate manual processes.   What is Power...

Read more
Would love your thoughts, please comment.x
Scroll to Top