Prorate charges: Splitting a row into multiple rows based on the number of days

[Anonymous]

hello,

I have a vendor invoice like below with start and end dates. I would like to split each row into actual value per month based on the number of days. 

is there an easy way to do this in Power Query or DAX?

thanks,

Sam

[Anonymous]

Hey all, 

this is the simplest way I found so far. The only missing part is that it does not consider contracts starting and ending in the same month but that can be fixed easily.

https://excelfort.com/allocate-amount-monthly-excel-powerquery/

thanks,
Samer

[AlienSx]

Hello, @Anonymous your calculations seem strange - why don't you count start date? You show 26 days in Decemeber (as 31 - 5) but what if your start date is Dec 31st? Anyway, suppose you have a table "charges" with columns "start", "end" and "amount". Suppose also that start date should be counted. Then this code

let
    Source = Excel.CurrentWorkbook(){[Name="charges"]}[Content],
    c_types = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}, {"amount", type number}}),
    f = (s as date, e as date, v as number) => 
        let 
            dur = Duration.Days(e - s) + 1, 
            d_cost = v / dur, 
            lst = List.Generate(
                () => [som = Date.StartOfMonth(s), eom = List.Min({Date.EndOfMonth(s), e}), d = Duration.Days(eom - s) + 1],
                (x) => x[som] <= e,
                (x) => [som = Date.AddMonths(x[som], 1), eom = List.Min({Date.EndOfMonth(som), e}), d = Duration.Days(eom - som) + 1],
                (x) => Record.SelectFields(x, {"som", "d"}, null)
            ),
            out = List.Transform(lst, each Record.TransformFields(_, {{"som", Date.MonthName}, {"d",  (x) => x * d_cost}}))
        in out,
    values_col = Table.AddColumn(c_types, "value", (x) => f(x[start], x[end], x[amount])),
    exp_list = Table.ExpandListColumn(values_col, "value"),
    exp_rec = Table.ExpandRecordColumn(exp_list, "value", {"som", "d"}, {"month", "prorated"})
in
    exp_rec

 generates the following table. You may use it as a source for further formatting (e.g. in pivot table) 

[Anonymous]

@AlienSx thanks a lot for the answer this looks good. will try it and let you know