#EXCEL EXPENSE CALENDAR INSTALL#

Note above that the calendar table has multiple rows for each month (4 or 5 depending on the month). I did another left outer join and selected the month column in both tables. Without saving this query (ie I kept on editing from the above point), I then selected Merge from the Power Query menu. Merge the Calendar Table with the above Monthly Budget Table I then expanded the new table so I could see the week count column (as shown below). 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). To do this, I simply started a new query by selecting the Merge option. Merge the Monthly Budget with the Week Count table Note I selected “only create connection” for this query too. I selected Group By (1 below), then selected to group by my MonthID (2 below) and selected Count Rows (3 below).Īnd this gave me my 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).

Note this is loaded to Power Query as Connection Only. This is the table that I loaded to the data model.ĭetailed Description of the process stepsįor 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). Finally I divide the monthly budget by the number of weeks in the month to create a weekly budget.The only trouble is that the budget on each line is a monthly budget and not a weekly budget 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). I then merged the calendar table from step 1 with the enhanced monthly budget table from step 3.My monthly budget table then shows how many weeks in each month as well as the monthly budget. Then I merged the monthly budget table with the Week Count table.I created a Week Count table that tells me how many weeks there are in each month using the calendar above.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). I used my 445 calendar as a mapping table.I think it is useful to outline what I did at a high level before showing the detailed steps. 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. 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). The problem is that they had some monthly budget figures that they wanted to track against actual sales on a weekly basis. I had an interesting problem this week with a client that uses a 445 calendar.

Relationships in Power BI and Power Pivot.

Who Needs Power Pivot, Power Query and Power BI Anyway?.

#EXCEL EXPENSE CALENDAR INSTALL#

The Best Way to Install Power BI Desktop.

30 Reasons You Should Be Considering Power BI.

Excel expense calendar