Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distribute pre-payments

I have the following table with payments. These are for pre-payments for a set of terms.

 

InvoiceNoInvoice DateAmountTerms
N00012-7-202060006
N00025-8-202015003
N00033-4-202073004
N00042-1-2020601

 

I want to report the turnover distributed over the months of the terms. So need a table like the below:

 

InvoiceNoTurnover DateAmount
N00012-7-20201000
N00012-8-20201000
N00012-9-20201000
N00012-10-20201000
N00012-11-20201000
N00012-12-20201000
N00025-8-2020500
N00025-9-2020500
N00025-10-2020500
N00033-4-20201825
N00033-5-20201825
N00033-6-20201825
N00033-7-20201825
N00042-1-202060

 

I am struggling with a DAX formula to create this table. I am working with GENERATESERIES, but stuck because the no. of terms is not always the same.

Thanks in advance for your help!

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous you should do this transformation in Power Query and create this table instead of DAX

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg,

      The solution on periodic billing helps me with constructing the solution.
      Thanks for your support.

       

      Bas

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    I think you could use M code to achieve this goal.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RczLDQAhCATQXjib8FW3im3A0H8bC2zUE8y8ZNaCl4gYGghOFBKKd0SVB7z9LpE6Ptu5l+txzYS2fWq5Hbfa57ufI+D+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [InvoiceNo = _t, #"Invoice Date" = _t, Amount = _t, Terms = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"InvoiceNo", type text}, {"Invoice Date", type date}, {"Amount", Int64.Type}, {"Terms", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {Number.From([Invoice Date])..Number.From(Date.AddDays([Invoice Date],[Terms]-1))}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each [Amount]/[Terms]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Invoice Date", "Amount"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "date"}, {"Custom.1", "amount"}})
in
    #"Renamed Columns"

    Best Regards,
    Zoe Zhi

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Zoe,

       

      Thanks for your reply.
      Very clever solution - but solution needs some modification for it to be workable for me as the terms are months, whereas your solution generates the terms based on days.

       

      Greetings,

      Bas