Power Query - Conversion of Annual budget to Daily/Monthly

Hi Folks, Today I got a call from my friend who encountered a problem related to Power BI and contacted me to help him out.

Problem Statment:

The client has provided him a budget file that looks like this

Now the issue is, he has recorded the total annual budget against an expense on the last date of the year. and when he creates a monthly report, it looks like this

As you see the total budget is showing against the last month and any kind of monthly analysis is quite difficult.

Question: Is there any way to convert this annual budget to daily/monthly budget in power query?

Answer = Yes

Solution:

Let's breakdown the solution in easy to understand steps

1. Create the start date for the budget year (In our case Fiscal calendar starts in July)

2. Create a List of dates between the start date and end date.

3. Get the end of the month for each date (as in my case budget is recorded at month end).

4. Recalculate budget value for months.

Step 1: Start Date for the year

For this, we will go to add a column and select the custom column

Now we will write a small piece of code to achieve our purpose

When we click ok, a Year start column will be there in our data

Step 2: Date range between Start and end date

Once the start date and end date are created, now we can write our main query which will look like this

Let’s break down the query to understand how it is working

Whenever we need to create a list between two numbers we can easily create it by use of curly brackets { }. if you write {1..5} power query will give you a list of numbers from 1 to 5. So I used the same concept here

1. Converted the “Year Start” column type from Date to Number by using “Number.From”. as the sequential list will only be created between numbers.

2. Use two dotes for Range.

3. Converted “Budget Year” column type from Date to Number by using “Number.From”.

4. Closed the query in Curly brackets.

If I use only this query, it will generate a number sequence which when converted to date type will give a sequential date list

But, I only want to get the last date of each month in the given year instead of the date range.

Step 3: Get the end-of-month date

Now let's focus on the other half of our code

1. “List.Transform” will require to link the list that we have just created “Date”

2. “Date.EndOfMonth” function will transform each date to the last date of that particular month.

3. “each” function will look into each date from the list and push it into the end of month query

4. End of Month query will require data type as Date for input, But we have used Number.From query to convert our Date into number in our Step 2, now we will use Date.From query to convert our number list into date.

5. “(_)” this underscore after Date.From will look into the current row and convert it into number.

6. After performing these steps, we will pack this query into “List.Distinct” function. This function will help us to get only a distinct month-end date instead of a month-end date for each date. For example, if we do not use this function, there will be 365 rows (approx 30 rows of each month's end date), after applying this function, there will only be 12 rows.

7. Now our query is complete, we just need to close the loop by passing the “in” statement

Now click on expand, and we now have 12 lines of each expense type for every month end

Step 4: Monthly budget value

The last step is to divide the budget value by twelve to get the proportional budget amount for each month

Divide pop-up will open, as I have an annual number, I simply divided it by 12 to get a monthly figure

Result:

Once all steps have been performed, load the close and apply the power query and change the create the relationship of date table with the budget table on month end date

Now we have automated the conversion of the annual budget to a monthly budget. The same step will be applied to convert the annual budget to the daily budget except

1. Step 3

2. and will use the number of days in a year for division in step 4
   

Cheers!!!