Forum Discussion

JGG's avatar
JGG
Icon for Helper I rankHelper I
5 years ago
Solved

Predict and distribute a payment plan

Hi!

 

I have different due dates and invoice amounts, for instance

20.07.2021: 100

23.07.2021: 90

Etc.

 

I need to predict the payments according to a plan like:

Due date: 75 % payment

Day 1-5: 2 % each day

Day 6-10: 1 % each day

Day 11-40: 0,2 % each day

 

If easier this could in fact be calculated by a logarthimic formula instead, something like f(x) = 75 + 1.8*log2((x+1)^2), with x from day 0 to 40 gives acceptable result.

 

I needs this in  a table with columns PayDay and Payment like this:

 

How to solve this?

 

Regards JG

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi JGG ，

     

    Try the following code in Power Query Editor:

     

    let
    Source = Table.FromValue(#"Table (2)"),
    #"Added Custom" = Table.AddColumn(Source, "PayDay", each List.Dates([due dates], 40, Duration.From(1))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "PayDay"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Payment", each if [PayDay]=[due dates] then [invoice amounts]*0.75 
                                                                                else if Duration.TotalDays([PayDay]-[due dates])<=5 then [invoice amounts]*0.02 
                                                                                    else if Duration.TotalDays([PayDay]-[due dates])<=10 then [invoice amounts]*0.01 
                                                                                        else [invoice amounts]*0.002),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"due dates", "invoice amounts"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Payment", type number}})
in
    #"Changed Type"

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

1 Reply

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi JGG ，

     

    Try the following code in Power Query Editor:

     

    let
    Source = Table.FromValue(#"Table (2)"),
    #"Added Custom" = Table.AddColumn(Source, "PayDay", each List.Dates([due dates], 40, Duration.From(1))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "PayDay"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Payment", each if [PayDay]=[due dates] then [invoice amounts]*0.75 
                                                                                else if Duration.TotalDays([PayDay]-[due dates])<=5 then [invoice amounts]*0.02 
                                                                                    else if Duration.TotalDays([PayDay]-[due dates])<=10 then [invoice amounts]*0.01 
                                                                                        else [invoice amounts]*0.002),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"due dates", "invoice amounts"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Payment", type number}})
in
    #"Changed Type"

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.