Automate Allocation of Amounts Across Months Using Power Query in Excel
Вставка
- Опубліковано 14 жов 2024
- 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:
excelfort.com/...
🟠 LET'S CONNECT!
-- / excelfort
-- / excelfort
-- / fowmy
Visit excelfort.com/... and subscribe to our blog for more content like this.
#powerquery #excel #advancedexcel #powerbi #msexcel
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.
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.
You are most welcome and gals it was helpful
Tell me about it... I had to develop a complex VBA routine!
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.
Glad it helped!
Excellent 👍👍
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)
Glad it was helpful,
Yes, thanks for your suggestion. I left it at that stage for the users to decide on how they wanted to analyze.
Appreciate your feedback!
Fantastic tutorial, saved me loads of time! Thank you very much!
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.
Thank you for this - You have just saved me loads of time!
Glad it helped!
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.
Glad it was helpful in your project and thanks for your comment
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?
Yes, it's possible but you need add an additional column identify the contracts like an index.
Hi, thank you for the video! I loved it!! Could you please explain how can I get daily amount instead of monthly amount?
Yes you can!
Simply add Custom Column as follows, then expand and Change data type to Date
{
Number.From([Start])..Number.From([End])
}
@@ExcelFort Hi can you specify this? I need to split by weeks...
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
Sorry, I having trouble understanding your question, can you elaborate with an example?
Awesome!! Thanks for sharing your knowledge. :-)
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...
Can do you define a week? Is it standard week number or is it like Monday to Sunday?
@@ExcelFort standard week number (week of year). E.g. I have a campaign running through three months, and I want to be able to track the spend on weeks.
@@ExcelFort I tried to replace month with week in the code but it seems like the commando Date.DaysInMonth doesn't exist for week. Any suggestions?
@@nataliedamstrup7363
You need to change the process in the function as it’s different from month based calculation. Please send a sample Excel file with the expected results to
info@excelfort.com
@@ExcelFort thanks i have send you a sample file.
ما شاءالله
اللهم زده فى علمه وانفع به برحمتك يا ارحم الراحمين
Thank you so much,
جزاكم الله خيرا
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
Hello nice, but what about date/time scenarios?
Could you provide more context please?
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
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
Thanks for pointing it out. I added a condition to handle periods falling within the same month.
Please download the updated file from the same location given in the description.
We are accountants not coders to have knowledge of M or other stupid language.
Appreciate your feedback! I totally get the frustration, especially for those not well-versed in coding. This particular video is tailored for those seeking guidance in constructing solutions using M code. However, I hear you, and I'll be putting together a new video soon on achieving the same result without any code, using just the interface. Stay tuned for an easier approach coming your way! 👍
@@ExcelFort Not Interested
@@ExcelFort I am interested but would need it rather sooner than later. Working with Office 365 and Power Query. Also, do you need full dates for this or can it be done by only using the month? I also would like to calculate month over month change as $$ and %.
The logic fails if the dates fall in the same month!
Thanks for pointing it out. I added a condition to handle periods falling within the same month.
Please download the updated file from the same location given in the description.
@@ExcelFort Thanks for updating the spreadsheet for fnPeriod -- Readers -- I noticed the only change you need to do is replace initial code until List.Generate().
let
Source = (pStart as date, pEnd as date) => let
Source =
if Date.EndOfMonth(pStart) = Date.EndOfMonth(pEnd) then
{[fDate=pStart , fDay = Duration.Days(pEnd-pStart)+1 ]}
else
List.Generate(
()=> [fDate = pStart, fDay= Date.DaysInMonth(fDate)-Date.Day(fDate)+1],
each [fDate] pEnd then
[fDate= Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)) , fDay= Date.Day(fDate)]
else
[fDate= EoM , fDay = Date.Day(fDate)]
),
in
Source
Dumb question .. is this DAX?
There is no such thing as a dumb question :D
It's not DAX, it's the M Language that works in Power Query, Please refer to this article: radacad.com/basics-of-m-power-query-formula-language#:~:text=M%20is%20informal%20name%20of,to%20know%20functions%20of%20it.
@@ExcelFort thank you!