Building a Fee payment table

[JoeHeraty]

Hi guys, 

New here, as struggling to find what I need anywhere online.  I'mlooking for some help in building a payment schedule for up to at least 12 months in advance, so I can predict dynamiclly and accurately what fees are coming in, in certain months, periods etc.

I have 3 fact fields, the payment frequency (monthly, Quarterly, Annual), the most recent paid date, and the amount.  I want for example the month names & year in columns and the values to populate the amount in the rows of the products.  I need a code to autopopulate based off the payment frequencys and the most recent payment date. 

The min date for the columns will be min date of the last issue date.  The max will vice versa.

Hope the community can help 🙂

Something to look similar to this: 

Frequency	Amount	Last Payment Date	Jan-25	Feb-25	Mar-25	Apr-25	May-25
Monthly	50	01/01/2025	50	50	50	50
Monthly	75	05/02/2025		75	75	75
Quarterly	100	01/01/2025	100			100
Quarterly	150	01/02/2025		150			150
Quarterly	250	20/01/2025	250			250
Annual	400	01/01/2025	400

[dufoq3]

Hi @JoeHeraty, I've created another query for you:

you can select DateType and WeekType

Output if you select Date = 1 (only weeks with payments)

Output if you select Date = 0 (full year)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTI1ABIGhvpAZGRgZKoUq4Msa24KkjXVNzBCyAaWJhaVpBaB5Q0NsGhHUQA3H4cJRmAFRgYoJjjm5ZUm5gDFTTDMjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Frequency = _t, Amount = _t, #"Last Payment Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Amount", Currency.Type}, {"Last Payment Date", type date}}, "sk-SK"),
    __Select__ = [ 
        DateType = 0,   // 0 = Full year,   1 = Use only weeks with payments
        WeekType = 0    // 1 = Ordinary,    1 = ISO
  ],
    D = [ firstDate = if (__Select__[DateType] ?? 0) = 0 then Date.StartOfYear(List.Min(ChangedType[Last Payment Date])) else List.Min(ChangedType[Last Payment Date]),
    lastDate = Date.EndOfYear(firstDate),
    weeks = Record.Combine(List.Generate(()=> firstDate, each _ <= lastDate, each Date.AddDays(_, 7), each Record.AddField([], Date.ToText(firstDate, "yyyy") & "-W" & Text.PadStart(Text.From(Date.WeekOfYear(_)), 2, "0"), null))) ],
    Fn_Week = (Data as date) =>
            let
                Weekday = Date.DayOfWeek(Data) + 1,
                Part1 = Number.From(Data) - Weekday + 11,
                Part2 = Number.From(#date(Date.Year(Date.From(Number.From(Data) + 4 - Weekday)),1,1)),
                Part3 = (Part1 - Part2) / 7,
                Tranc = Part3 - Number.Mod(Part3, 1),
                Output = Date.ToText(D[firstDate], "yyyy") & "-W" &
                                ( if __Select__[WeekType] = 1
                                  then Text.PadStart(Text.From(Tranc), 2, "0")
                                  else Text.PadStart(Text.From(Date.WeekOfYear(Data)), 2, "0")
                                )
            in
                Output,
    StepBack = ChangedType,
    Ad_Weeks = Table.AddColumn(StepBack, "Weeks", each 
        [ a = if [Frequency] = "Annual" then {Record.AddField([], Fn_Week([Last Payment Date]), [Amount])} else List.Generate(
                    ()=> D[firstDate],
                    (x)=> x <= D[lastDate],
                    (x)=> if [Frequency] = "Weekly" then Date.AddDays(x, 7) else
                            if [Frequency] = "Monthly" then Date.AddMonths(x, 1)
                            else Date.AddQuarters(x, 1),
                    (x)=> Record.AddField([], Fn_Week(x), [Amount])),
          b = _ & Record.Combine(a)
        ][b]
    , type table),
    UsedSelectedDate = [ fn = each Table.Skip(Table.Combine(List.Transform(_[Weeks], (x)=> Table.FromRecords({x})))),
    a = Table.InsertRows(Ad_Weeks, 0, { Ad_Weeks{0} & [Weeks = Record.Combine(List.Transform(List.RemoveMatchingItems(Table.ColumnNames(Ad_Weeks), {"Weeks"}), (x)=> Record.AddField([], x, null))) & D[weeks]] }),
    b = if __Select__[DateType] = 1 then fn(Ad_Weeks) else fn(a)
  ][b],
    ChangedType2 = Value.ReplaceType(
        UsedSelectedDate,
        Value.Type(Table.FirstN(ChangedType, 0) &
          [ a = List.Difference(Table.ColumnNames(UsedSelectedDate), Table.ColumnNames(ChangedType)),
            b = Table.PromoteHeaders(Table.FromRows({a})),
            c = Table.TransformColumnTypes(b, List.Transform(a, (x)=> {x, Currency.Type}))
          ][c] ))
in
    ChangedType2

[JoeHeraty]

Hi dufoq, thank you very much! This is awesome!

[dufoq3]

You're welcome @JoeHeraty. Enjoy 😉

[v-mdharahman]

Hi @JoeHeraty,
Thanks for reaching out to the Microsoft fabric community forum.

It looks like the issue you were having has been solved by @jgeddes. Please accept it as solution if you are satisfied with the solution.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.

[AlienSx]

let
    // fact_table is your original table
    fx_schedule = (row) => ((sequence) => Record.FromList(List.Repeat({row[Amount]}, List.Count(sequence)), sequence))
        (List.Generate(
            () => row[Last Payment Date], 
            (x) => x <= last_month, 
            (x) => Function.Invoke(Record.Field(freq, row[Frequency]), {x, 1}),
            (x) => Date.ToText(x, "MMM-yy")
        )),
    freq = [Monthly = Date.AddMonths, Quarterly = Date.AddQuarters, Annual = Date.AddYears],
    last_month = Date.AddMonths(Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())), 12), 
    headers = List.Generate(
        () => List.Min(fact_table[Last Payment Date]), 
        (x) => x <= last_month, 
        (x) => Date.AddMonths(x, 1),
        (x) => Date.ToText(x, "MMM-yy")
    ),
    schedule = Table.AddColumn(fact_table, "expand_me", fx_schedule), 
    xpand = Table.ExpandRecordColumn(schedule, "expand_me", headers)
in
    xpand

[jgeddes]

Here is an example code that shows one way to do this.
You can change the endYear value to lengthen or shorthen the forecast period.

let
    endYear = 2026,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTI1ABKG+ob6RgZGpkqxOshy5qZAwkjfFCEXWJpYVJJaBJY1NMDQiiINNtkIl7QR1GIjA4S8Y15eaWIOUNgEzehYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Frequency = _t, Amount = _t, #"Last Payment Date" = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Frequency", type text}, {"Amount", Int64.Type}, {"Last Payment Date", type date}}),
    add_freq_months = Table.AddColumn(set_types, "Frequency Months", each if [Frequency] = "Monthly" then 1 else if [Frequency] = "Quarterly" then 3 else if [Frequency] = "Annual" then 12 else 0, Int64.Type),
    generate_paid_months = Table.AddColumn(add_freq_months, "Dates", each let freq = [Frequency Months], startDate = Date.StartOfMonth([Last Payment Date]), amount = [Amount] in List.Generate(()=> startDate, each _ <= #date(endYear,12,1), each Date.AddMonths(_, freq), each Date.ToText(_)&"|"&Number.ToText(amount))),
    convert_list_to_table = Table.TransformColumns(generate_paid_months, {{"Dates", each Table.FromList(_, Splitter.SplitTextByDelimiter("|"), {"Date", "Amount"})}}),
    transpose_nested_tables = Table.TransformColumns(convert_list_to_table, {{"Dates", each Table.PromoteHeaders(Table.Transpose(_))}}),
    minPaymentMonth = Date.StartOfMonth(List.Min(transpose_nested_tables[Last Payment Date])),
    minFrequencyMonths = List.Min(transpose_nested_tables[Frequency Months]),
    generatedDatesList = List.Generate(()=> minPaymentMonth, each _ <= #date(endYear,12,1), each Date.AddMonths(_, 1), each Date.ToText(_)),
    expand_nested_tables = Table.ExpandTableColumn(transpose_nested_tables, "Dates", generatedDatesList),
    remove_freq_months = Table.RemoveColumns(expand_nested_tables,{"Frequency Months"})
in
    remove_freq_months

Using your example data...

You end up with...

Hope this gets you pointed in the right direction.

[JoeHeraty]

Thank you very much! This is exactly what I needed!  Where in the code could I alter to get column headers as Week Number and values to fill in the week periods, or would this need complete new code?

[jgeddes · Accepted solution]

Here is the code adjusted to display by week. (I have weeks starting on Monday.)

let
    endYear = 2026,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTI1ABKG+ob6RgZGpkqxOshy5qZAwkjfFCEXWJpYVJJaBJY1NMDQiiINNtkIl7QR1GIjA4S8Y15eaWIOUNgEzehYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Frequency = _t, Amount = _t, #"Last Payment Date" = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Frequency", type text}, {"Amount", Int64.Type}, {"Last Payment Date", type date}}),
    add_freq_months = Table.AddColumn(set_types, "Frequency Months", each if [Frequency] = "Monthly" then 1 else if [Frequency] = "Quarterly" then 3 else if [Frequency] = "Annual" then 12 else 0, Int64.Type),
    generate_paid_months = Table.AddColumn(add_freq_months, "Dates", each let freq = [Frequency Months], startDate = Date.StartOfMonth([Last Payment Date]), amount = [Amount] in List.Generate(()=> startDate, each _ <= #date(endYear,12,1), each Date.AddMonths(_, freq), each "Week " & Number.ToText(Date.WeekOfYear(_, Day.Monday))&"|"&Number.ToText(amount))),
    convert_list_to_table = Table.TransformColumns(generate_paid_months, {{"Dates", each Table.FromList(_, Splitter.SplitTextByDelimiter("|"), {"Date", "Amount"})}}),
    transpose_nested_tables = Table.TransformColumns(convert_list_to_table, {{"Dates", each Table.PromoteHeaders(Table.Transpose(_))}}),
    minPaymentWeek = Date.WeekOfYear(Date.StartOfMonth(List.Min(transpose_nested_tables[Last Payment Date])), Day.Monday),
    generatedDatesList = List.Generate(()=> minPaymentWeek, each _ <= Date.WeekOfYear(#date(endYear,12,1), Day.Monday), each _ + 1, each "Week " & Number.ToText(_)),
    expand_nested_tables = Table.ExpandTableColumn(transpose_nested_tables, "Dates", generatedDatesList),
    remove_freq_months = Table.RemoveColumns(expand_nested_tables,{"Frequency Months"})
in
    remove_freq_months

[JoeHeraty]

Jgeddes, this is brilliant thank you very much!