Automate Allocation of Amounts Across Months Using Power Query in Excel

Подписаться 13 тыс.

Просмотров 19 тыс.

50% 1

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/allocate-amount...
🟠 LET'S CONNECT!
-- / excelfort
-- / excelfort
-- / fowmy
Visit excelfort.com/.com and subscribe to our blog for more content like this.
#powerquery #excel #advancedexcel #powerbi #msexcel

Опубликовано:

10 июн 2020

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 47

@6504s.p 3 месяца назад

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

@philipperoch7668 2 года назад

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.

@ExcelFort 2 года назад

Glad it helped!

@timwalker900 3 года назад

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

@ExcelFort 3 года назад

Glad it helped!

@ingalepn 3 года назад

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)

@ExcelFort 3 года назад

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!

@rwps9956 Год назад

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

@ExcelFort Год назад

Thank you so much, ‏جزاكم الله خيرا

@Shiffo 2 года назад

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.

@ExcelFort 2 года назад

You are most welcome and gals it was helpful

@cristoumanzor3896 5 месяцев назад

Tell me about it... I had to develop a complex VBA routine!

@shubalubla 2 года назад

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.

@ExcelFort 2 года назад

Glad it was helpful in your project and thanks for your comment

@cristoumanzor3896 5 месяцев назад

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

@harkiratsingh571 3 года назад

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

@ExcelFort 3 года назад

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.

@cdalav 3 года назад

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?

@ExcelFort 3 года назад

Yes, it's possible but you need add an additional column identify the contracts like an index.

@toirshad 3 года назад

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

@ExcelFort 3 года назад

Yes you can! Simply add Custom Column as follows, then expand and Change data type to Date { Number.From([Start])..Number.From([End]) }

@nataliedamstrup7363 2 года назад

@@ExcelFort Hi can you specify this? I need to split by weeks...

@reddodger78 2 года назад

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

@chandrapathak2951 2 года назад

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

@ExcelFort 2 года назад

Sorry, I having trouble understanding your question, can you elaborate with an example?

@nataliedamstrup7363 2 года назад

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...

@ExcelFort 2 года назад

Can do you define a week? Is it standard week number or is it like Monday to Sunday?

@nataliedamstrup7363 2 года назад

@@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.

@nataliedamstrup7363 2 года назад

@@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?

@ExcelFort 2 года назад

@@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

@nataliedamstrup7363 2 года назад

@@ExcelFort thanks i have send you a sample file.

@ajithkg6221 Год назад

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

@lionels839 Год назад

Hello nice, but what about date/time scenarios?

@ExcelFort Год назад

Could you provide more context please?

@thecaroconcept 2 года назад

Dumb question .. is this DAX?

@ExcelFort 2 года назад

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.

@thecaroconcept 2 года назад

@@ExcelFort thank you!

@harkiratsingh571 3 года назад

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

@ExcelFort 3 года назад

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.

@ingalepn 3 года назад

@@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

@farooqtahir538 8 месяцев назад

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

@ExcelFort 8 месяцев назад

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! 👍

@farooqtahir538 8 месяцев назад

@@ExcelFort Not Interested

@lilianm7151 3 месяца назад

@@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 %.