Allocating amounts such as contracts, prepayment, revenue, cost, etc. across different date ranges is a common Excel challenge that can be solved in many ways. Let’s assume you have some rent contracts with start date, end date, and the amount which needs to be split into months based on the exact number of days in each month.
For Con-01 in the table below, the amount of 110,000 has to be divided by 291 days and multiplied by 22 days in Jan-2020, 29 days in Feb-2020 and so on, Oct-2020 will have 26 days. looks pretty simple?
The challenge is that any addition of new contracts or changes in contract periods should automatically adjust the number of rows and columns, in the meantime calculating the monthly amounts correctly as explained above.
The Pivot Table below shows the desired result that we are trying to achieve here. It is giving the flexibility to analyze the above contracts in various date dimensions, even with a large number of records.
When I started searching on the web on this topic, I came across Chris Webb’s blog where he uses a neat technique in Power Query to allocate amounts equally across months. I also found another blog over at Excel University blog where Excel formulas are used cleverly to solve somewhat similar challenges but, I had to come up with the following Power Query solution as I wanted the exact number of days in each month for allocation.
If you would like to watch the video version of this post, please click below or continue reading as I explain the steps briefly assuming that you are already familiar with Power Query.
You can download the Excel file for this post and follow along, even use it as a template in a similar allocation needs.
Power Query Steps
- First, I imported the Rent Contracts table into Power Query and changed the data type of the Start and End Date to DATE then renamed the Query to Contracts.
- The key element of this solution is the function that I created to generate a list of months and days in each month. I then setup two parameters (pStart and pEnd) for the function to receive the Start and End dates of each contract.
- Next, I built a query which will be converted to a function later starting with a list of records using the List.Generate function which is so powerful in scenarios like this.
This was the challenging part in the whole process.
- Now I have got a nice list of records, rest of the steps are simple
- I converted the List of records on to a table and expanded the records
- Set proper Data Type for fDate and fDay fields as DATE and WHOLE NUMBER
- Then, I created a function named fnPeriod from the query using the pStart and pEnd parameters. you can see the completed function below.
- Next, I invoked the function in the Contract table passing the Start and End dates to the parameters pStart and PEnd respectively.
- Expanded the newly added column, now I have two columns giving me the month and days in each month.
- Added one more column as “Monthly Amount” to calculate the proportionate amount for each month:
[Amount]/[Days] * [fDay])
- After removing unnecessary columns, the query is ready to be loaded onto a worksheet.
- Now, you can use this table to build a Pivot Table to do the analysis.
- Refreshing the query will automatically update the results for any contract addition or modifications.
- The final query for the the above table is below and the file can be downloaded from here.
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, each let EoM = Date.EndOfMonth(Date.AddMonths([fDate], 1)) in if EoM > pEnd then [ fDate = Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)), fDay = Date.Day(fDate) ] else [fDate = EoM, fDay = Date.Day(fDate)] ), #"Converted to Table" = Table.FromList( Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Expanded Column1" = Table.ExpandRecordColumn( #"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"} ), #"Changed Type" = Table.TransformColumnTypes( #"Expanded Column1", {{"fDate", type date}, {"fDay", Int64.Type}} ) in #"Changed Type" in Source
Conclusion
I hope you found this article useful and learned some new techniques as well? If you have any challenging allocation issues, feel free to share it with me.
Please leave your comments and questions below.
Have a nice day!
I used Power Query formatter to format and embed M codes in this blog. It’s in BETA.
Fowmy Abdulmuttalib is a Microsoft-certified business intelligence professional with extensive experience in finance, accounting and data analytics. Currently, he is employed by McDonald’s Saudi Arabia as the Head of ERP and Business Intelligence. He is interested in everything data-related, he likes to share his passion and knowledge with the data community.