Split Amount Across Months Using Power Query

In this article, we will look at how to solve an allocation issue where you need to deal with start and end dates falling within different dates in months.

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. 

 

 

Power Query Steps: 

 

• Imported the contracts table from the Excel file (Find the Excel and the Power BI files below my signature)
• Change the data types as above if required.
• Create two parameters for the start and end dates. I named them pStart and pEnd
• The key element of this solution is the function that I created to generate a list of months and days in each month. I used the two parameters (pStart and pEnd) for the function to receive the Start and End dates of each contract.
• Create a blank query and paste the following code. I named my function as fnPeriod. The List.Generate function is a little complicated to understand but once you learn it, you will love it.

 

 

 

 

 

 

(pStart as date, pEnd as date) => let
        Source = 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)]
    ),
        ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandTable = Table.ExpandRecordColumn(ToTable, "Column1", {"fDate", "fDay"}, {"fDate", "fDay"}),
        ChangedType = Table.TransformColumnTypes(ExpandTable,{{"fDate", type date}, {"fDay", Int64.Type}})
    in
        ChangedType

 

 

 

 

 

 

• 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 a custom column, “Monthly Amount” to calculate the proportionate amount for each month.
• Added another custom column “Month Year” to extract the end of the month for the dates in order to analyze by months.
  
• Remove unnecessary columns and uploaded the query to the data model to visualize as shown above in the matrix.
  
  Download the Power BI and the source data file attached below

  I hope you found this article useful and learned some new techniques as well