Convert Monthly Budget into Weekly Buckets 445 Calendar

I had an interesting problem this week with a client that uses a 445 calendar.  The problem is that they had some monthly budget figures that they wanted to track against actual sales on a weekly basis.  We agreed to do a straight line split of the budget into weeks (ie the weekly budget will be the monthly budget divided by 4 for a 4 week month, and divided by 5 for a 5 week month).  Now it is possible to do this in DAX within a measure, but I decided to materialise the monthly budget table into a weekly budget table using Power Query and then load the weekly table to Power Pivot. Here is how I did it.

Overview of Steps

I think it is useful to outline what I did at a high level before showing the detailed steps.  This is what I did.

1. I used my 445 calendar as a mapping table.  The calendar already has a YYMM column and a YYWW column that maps each week number to each month number (some months have 4 weeks, some have 5).  This is needed in the steps below.
2. I created a Week Count table that tells me how many weeks there are in each month using the calendar above.
3. Then I merged the monthly budget table with the Week Count table.  My monthly budget table then shows how many weeks in each month as well as the monthly budget.
4. I then merged the calendar table from step 1 with the enhanced monthly budget table from step 3.  This has the effect of replicating each monthly budget line in the original table and creating a weekly budget table (4 rows for a 4 week month and 5 rows for a 5 week month).  The only trouble is that the budget on each line is a monthly budget and not a weekly budget
5. Finally I divide the monthly budget by the number of weeks in the month to create a weekly budget.  This is the table that I loaded to the data model.

Detailed Description of the process steps

For this demo, I started with the 445 Calendar table I created using Power Query in this blog post and made some changes to turn it into a weekly calendar (literally I filtered for Day = Saturday, changed the Date column to “Week Ending Date” and created new ID columns for YYWW and YYMM).

Here is my starting Weekly 445 Calendar

Here is my Monthly Budget Table

Note this is loaded to Power Query as Connection Only.

Create a Week Count Table

To do this, I right clicked on my Calendar Table query (1 below) and created a new query with reference (2 below).

I selected Group By (1 below), then selected to group by my MonthID (2 below) and selected Count Rows (3 below).

And this gave me my Week Count table.  Note I selected “only create connection” for this query too.

Merge the Monthly Budget with the Week Count table

To do this, I simply started a new query by selecting the Merge option.

I selected the MonthlyBudget table (1 below) to join to the WeekCount table (2 below) and selected the common YYMM column to join (3 & 4 below) as a left outer joint (5 below).

I then expanded the new table so I could see the week count column (as shown below).

Merge the Calendar Table with the above Monthly Budget Table

Without saving this query (ie I kept on editing from the above point), I then selected Merge from the Power Query menu.  I did another left outer join and selected the month column in both tables.

Note above that the calendar table has multiple rows for each month (4 or 5 depending on the month).  When you join two tables like this that have a 1 to many relationship (1 row in the budget table matches many rows in the calendar table) it has the effect of replicating the rows of the first table once for every match in the second table.  After I expanded the joined table to additional show the YYWW column, you can see that the Budget Table now has 52 rows instead of the original 12 as shown below.

Almost done.  All that is left is to divide the Budget column (which is the monthly budget) by the number of weeks to get the weekly budget column.  Then load this final table into my data model.

Here is the workbook I used if you would like to take a look.

if you want a comprehensive lesson on how to use Power Query, checkout my training course here https://xbi.com.au/pqt