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!


Learn and 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

List.Generate() and Looping in PowerQuery

In this blog, we will explain an advanced topic about the M language – using List.Generate to loop over a table. We will combine our knowledge of lists, records, functions, iterations, and looping techniques. Basically, every important object in PowerQuery. Do-while Loop in PowerQuery Do while loop is commonly used...

Read more

Using M to dynamically change column names in PowerQuery

In this article, we are going to talk about cleaning and transforming column names dynamically and in a bulk. The article was inspired by a request from a client who had issues exporting data from its data warehousing program to .csv files. Out of an unknown reason, program was adding...

Read more

Pivottables inside the PowerBI – The DAX way

PivotTables, or pivoting, is by far the best analytical power of Excel. You digest the data and play with it on the fly. Heck, PowerBI is pretty much PivotTables with pretty visuals, just a lot smarter (because of DAX). While PowerBI offers many stunning looking visuals including matrices, they do...

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