Prorated annual expense

[mwebergo2]

I manage multiple vendors with various contract start/end dates and annual contract amount.  I am trying to extrapolate this contract amount into monthly amounts so that I can reflect an annual calendar year sum.  Note that contracts have various start and end dates, but are always an annual amount.

In the example below, the annual rate of $30,000 should be reflected as 12 monthly amounts of $2,500 from June 2023 until May of 2024, and 12 monthly payments of $2,625 from June 2024 until May 2025, and so on.

For 2023, the sum amount for Vendor 1 / system 1 should be $17,500 ($2,500 a month for Jun - Dec).  For 2024, it should be $30,875 which is $12,500 for Jan - May 2024 plus $18,375 for Jun - Dec 2024 (rate increases midway through the year).

Vendor	System	Start date	End Date	Annual Contract Amt
Vendor 1	System 1	6/1/2023	5/31/2024	$                         30,000.00
Vendor 1	System 1	6/1/2024	5/31/2025	$                         31,500.00
Vendor 1	System 1	6/1/2025	5/31/2026	$                         33,075.00
Vendor 1	System 1	6/1/2026	5/31/2027	$                         34,728.75
Vendor 1	System 1	6/1/2027	5/31/2028	$                         36,465.19
Vendor 1	System 1	6/1/2028	5/31/2029	$                         38,288.45
Vendor 1	System 2	6/1/2023	5/31/2024	$                            5,000.00
Vendor 1	System 2	6/1/2024	5/31/2025	$                            5,250.00
Vendor 1	System 2	6/1/2025	5/31/2026	$                            5,512.50
Vendor 1	System 2	6/1/2026	5/31/2027	$                            5,788.13
Vendor 1	System 2	6/1/2027	5/31/2028	$                            6,077.53
Vendor 1	System 2	6/1/2028	5/31/2029	$                            6,381.41
Vendor 2	System 3	3/16/2023	3/15/2024	$                            2,600.00
Vendor 2	System 3	3/16/2024	3/15/2025	$                            2,730.00
Vendor 2	System 3	3/16/2025	3/15/2026	$                            2,730.00
Vendor 2	System 3	3/16/2026	3/15/2027	$                            2,730.00
Vendor 2	System 3	3/16/2027	3/15/2028	$                            2,730.00
Vendor 2	System 3	3/16/2028	3/15/2029	$                            2,730.00
Vendor 4	System 4	1/1/2022	12/31/2022	$                         26,117.60
Vendor 4	System 4	1/1/2023	12/31/2023	$                         25,320.00
Vendor 4	System 4	1/1/2024	12/31/2024	$                         44,310.00
Vendor 4	System 4	1/1/2025	12/31/2025	$                         44,310.00
Vendor 4	System 4	1/1/2026	12/31/2026	$                         44,310.00
Vendor 4	System 4	1/1/2027	12/31/2027	$                         44,310.00
Vendor 4	System 4	1/1/2028	12/31/2028	$                         44,310.00

[mwebergo2]

This is awesome and perfectly addressed what I was trying to do.  All of my contracts are reflected with a start date on the 1st of the month so I don't need th fractional but that is fine.  Thank you so much!

[AlienSx]

let
    fx_propated = (start, end, amount) => ((monthly_rate) => List.Generate(
        () => 
            [
                som = Date.StartOfMonth(start), 
                value = monthly_rate * (Duration.Days(List.Min({Date.EndOfMonth(start), end}) - start) + 1) / Date.DaysInMonth(start)
            ],
        (x) => x[som] < end,
        (x) => 
            [som = Date.AddMonths(x[som], 1), 
            value = if Date.StartOfMonth(end) = som 
                then monthly_rate * (Duration.Days(end - som) + 1) / Date.DaysInMonth(end)
                else monthly_rate
            ],
        (x) => Record.FieldValues(x)
    ))(amount / 12),
    // contracts is your original table
    Source = contracts, 
    rows = List.TransformMany(
        Table.ToList(Source, each _),
        (x) => fx_propated(x{2}, x{3}, x{4}),
        (x, y) => List.FirstN(x, 5) & y
    ), 
    result = Table.FromList(rows, each _, Table.ColumnNames(Source) & {"Month", "Amount"})
in
    result

[OwenAuger · Accepted solution]

Hi @mwebergo2 

For this, I would expand each row into 12 rows, each containing a date within the months between Start/End dates and 1/12 of the annual amount. We can use the first date the month as an arbitrary date if you are reporting at a monthly level.

For example, the first row (Vendor 1/System 1 for June 2023 - May 2024):

Vendor	System	Start date	End Date	Annual Contract Amt
Vendor 1	System 1	2023-06-01	2024-05-31	30,000.00

would expand to:

Vendor	System	Date	Amount
Vendor 1	System 1	2023-06-01	2,500.00
Vendor 1	System 1	2023-07-01	2,500.00
Vendor 1	System 1	2023-08-01	2,500.00
Vendor 1	System 1	2023-09-01	2,500.00
Vendor 1	System 1	2023-10-01	2,500.00
Vendor 1	System 1	2023-11-01	2,500.00
Vendor 1	System 1	2023-12-01	2,500.00
Vendor 1	System 1	2024-01-01	2,500.00
Vendor 1	System 1	2024-02-01	2,500.00
Vendor 1	System 1	2024-03-01	2,500.00
Vendor 1	System 1	2024-04-01	2,500.00
Vendor 1	System 1	2024-05-01	2,500.00

One question that would help with implementing this in Power Query:

• How do you want to handle contracts that begin/end part way through a month, such as Vendor 2/System 3 which starts on 16-March and ends on 15-March?
  Would you allocate 1/2 the monthly amount to 16-March in the start year and 1/2 the monthly amount to 1 March in the end year (with all other months getting a full monthly amount)?
  Or would you just calculate a month fraction based on days, i.e. 16/31 in March of start year and 15/31 in March of end year?

I have attached an example assuming each month's allocation is based on the day count as a fraction of the month.

The Contract table ends up like this in my example:

The Power Query code for the Contract table is as follows (assuming your original query is named ContractSource).

The query removes the original Start Date, End Date and Amt columns, but you could keep these if useful for reporting purposes.

let
  Source = ContractSource,
  #"Added Month Offset" = Table.AddColumn(
    Source,
    "Month Offset",
    each
      let
        EndMonthOffset = (Date.Year([End Date]) - Date.Year([Start Date]))
          * 12
          + (Date.Month([End Date]) - Date.Month([Start Date]))
      in
        {0 .. EndMonthOffset},
    type {Int64.Type}
  ),
  #"Expanded Month Offset" = Table.ExpandListColumn(#"Added Month Offset", "Month Offset"),
  #"Added DateAmount Record" = Table.AddColumn(
    #"Expanded Month Offset",
    "DateAmount",
    each
      let
        MonthStart = Date.AddMonths(Date.StartOfMonth([Start Date]), [Month Offset]),
        MonthEnd = Date.AddMonths(Date.EndOfMonth([Start Date]), [Month Offset]),
        EffectiveStart = List.Max({[Start Date], MonthStart}),
        EffectiveEnd = List.Min({[End Date], MonthEnd}),
        MonthFraction = (Number.From(EffectiveEnd - EffectiveStart) + 1)
          / (Number.From(MonthEnd - MonthStart) + 1),
        Amount = [#"Annual Contract Amt"] / 12 * MonthFraction
      in
        [Date = EffectiveStart, Amount = Amount],
    type [Date = date, Amount = number]
  ),
  #"Expanded DateAmount" = Table.ExpandRecordColumn(
    #"Added DateAmount Record",
    "DateAmount",
    {"Date", "Amount"},
    {"Date", "Amount"}
  ),
  #"Removed Columns" = Table.RemoveColumns(
    #"Expanded DateAmount",
    {"Month Offset", "Start Date", "End Date", "Annual Contract Amt"}
  )
in
  #"Removed Columns"

Sample visual (with a Date table added):

Is this close to what you're looking for?