Prorated annual expense

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?