Power Query

Equal Allocation of Budget in Power BI

The equal allocation of budget is the technique you use to compare budget information at the different granularity with other data such as Sales or Discounts etc.using Power Query. They are an extension of the handling of different granularities in Power BI using the DAX as I discussed in my last blog. In this article, I will use Power Query to develop Equal allocation pattern to allocate the budget at day granularity for which it is not available.

Last Week, I had to deal with the similar problem from my client where they would like to allocate the monthly budgets equally at the day level and this would require us to write custom MDX SCOPE Statements in the cube. This led me to the thought that how can we achieve this in Power BI. Do you want to explore how I did it?

Let’s join me in this Power BI Journey to learn this new technique.

Suppose you have budget for each product at the month level, as shown in Figure 1.

budget 6.png

and I would like to achieve the final result, as shown in the Figure 2.

budget 7.png

To get this end result, I have first created a Calendar Table in Power Query.
In the Blank Query, Open the Advanced Editor, Copy and Paste the below code to create a Calendar Table.

budget 3

//Create Date Dimension
(StartDate as date, EndDate as date)=>
 //Capture the date range from the parameters
 StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
 EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
//Get the number of dates that will be required for the table
 GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
 GetDateList = List.Dates(StartDate, GetDateCount, 
//Convert the list into a table
 DateListToTable = Table.FromList(GetDateList, 
 Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
 //Add Year Column
 YearNumber = Table.AddColumn(DateListToTable, "Year", 
 each Date.Year([Date])),
//Add Quarter Column
 QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
 each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
 WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
 each Date.WeekOfYear([Date])),
//Add Month Number Column
 MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
 each Date.Month([Date])),
//Add Month Name Column
 MonthName = Table.AddColumn(MonthNumber , "Month", 
 each Date.ToText([Date],"MMMM")),
//Add Day of Week Column
 DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
 each Date.ToText([Date],"dddd"))

Now, We will add few new columns (BudgetID, MonthNo, MonthName & Total Days) in the Budget Table.

budget 5.png

To allocate the monthly budgets at the day granularity in the Budget Table, I have created a new custom column by simply dividing the BudgetAmount with TotalDays.

budget 8

Next, I have merged the Calendar and Budget Table with Month Name as the join key, as shown in the below figure.

budget 9

I then just need to click on the expand icon next to the column header of the Budget Column  to repeat each equal allocation amount for all the days in specific months it applies to, rename the columns and set the column types appropriately.

budget 10.png

Last of all, I need to create day column in the Calendar Table by duplicating the Date Column & converting it into the Day column. This is required to use the Sort by Column functionality to sort the Date Column. Finally, format my Allocated Amount column using a dollar sign.

Now, I’m ready to load the data into the Power BI.

One last point, Disable the load of the Budget Table in the Data model. This will make your data model cleaner and clutter free.

You can also create a Year Hierarchy in the Calendar Table and use the Matrix Visual to display the correctly allocated values in the Power BI.

budget 12.png

This technique can also be extended to equally allocate the Year/Quarterly budgets at the lower granularities.

You can download the demo workbook here.


Categories: Power Query

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s