Automate Allocation of Amounts Across Months Using Power Query in Excel

Let’s assume you have an amount, start date, and end date. You need to split that amount into months based on the exact number of days in each month. also, keep in mind that the start and end months may be incomplete. This video shows you exactly how to do it using Power Query in Excel.

Though there are many approaches to it, the dynamic Power Query method makes it super easy to manage many records without having to worry about Excel Formulas. Moreover, you can copy these Power Query Queries to Power BI and perform your analysis. There are many use cases such as spreading prepayment, revenue and cost across different date range where this template will become useful.

🟠 You may download the Excel file here:

🟠 LET'S CONNECT!

#powerquery #excel #advancedexcel #powerbi #msexcel

[medinatrent-borja]

Thank you for this amazing video! However, I’m getting a parse error when loading and can’t figure out why. The data displays perfectly in power query editor.

[philandjana]

Thanks - this is great! Additional logic can be added so that it handles entries where pStart and pEnd are in the same month (100% goes to the single month), otherwise the amount calculated incorrectly for the single month.

[reddodger]

I am getting an Expression Error: Column 1 cannot be found in the table after I have expanded the query table and invoked the custom function. Can any one help with a work around . . otherwise this is the perfect anser to a problem I am trying to solve

[farooqtahir]

We are accountants not coders to have knowledge of M or other stupid language.

[harkiratsingh]

We should also add a condition which states that if the start and end fall in the same month and year then take the amount as is otherwise split it in other months

[rwps]

ما شاءالله
اللهم زده فى علمه وانفع به برحمتك يا ارحم الراحمين

[chandrapathak]

Thanks a lot for sharing. How to add End date also for each month like you have mentioned Start date and days, same way start date, end date and days in a month. What if I have to follow 360 days in a year and 30 days in a month can you incorporate this into your solution ...Help me

[philipperoch]

Hi, Thank you so much for this video! I downloaded the excel file, personalized it for my need and everything is working! You saved me so much time.

[ingalepn]

I liked it. It will save my Finance Managers quite a lot of time when we have multi-year reports. Helped in allocating our 14-day payroll into months. One update at the end I would suggest. Instead of Pivot in excel, do pivot in power query. Steps: 1. right-click fDate and transform to Month-- end of Month. Then group by contract, fDate and Amount. Followed by pivot in power query (select fDate column, click menu\transform\pivot columns, select values column as Amt and aggregate value function as SUM)

[timwalker]

Thank you for this - You have just saved me loads of time!

[ajithkg]

How do we tweak such a way that the amounts are allocated based on months and not based on days? Say if i have contract between Jan to Mar, i would want to have same amount in each of the month rather by days. In excel i have used EOMonth formula and using helper cells i have made my spreadsheet. But if you can help in power query, that would be of great help

[cdalav]

Fantastic! What if we have modifications in contracts in both dates and amounts? Can we keep the latest when we overlap and maintain the older ones when we don't?

[cristoumanzor]

Awesome!! Thanks for sharing your knowledge. :-)

[s.p]

Fantastic tutorial, saved me loads of time! Thank you very much!

[lionels]

Hello nice, but what about date/time scenarios?

[Shiffo]

WOW! I spent 1 or 2 days building complex excell formula's to calculate the monthly revenue with manual errors I needed to fix for every line when starting date or ending date was not the EoM.
This sheet really helps me a lot and does not involve to complex code.

Thank you so much for sharing this method.

[toirshad]

Hi, thank you for the video! I loved it!! Could you please explain how can I get daily amount instead of monthly amount?

[shubalubla]

Hey Buddy,

just want to say that me and my work partner just finish a very important phase of our power query system based on your video.

So thanks very much for that, it was very didatic and well done.

Best Regards from Brazil.

[nataliedamstrup]

Hi thanks for your tutorial!
Can you specify how I can get daily amount (preferably weekly) instead of monthly amount? I need to split a monthly amount by weeks...

[harkiratsingh]

The logic fails if the dates fall in the same month!