Split the payments in contact

[Stanislavp]

Hello MS-BI Community,

I am new here and I want to ask for help with following issue.

In my data model i have a few contracts 

Each contract have a payments terms ( in my case its 30% prepayment 70% after 180 days shippment day 

In contract i can have several shipments : 

We have the contract ( 100$) 

and 3 shipments 

30$ - June - so the payment will be ( 30% - 9$ 70% -21$ ) 

20$- July 

50$-Аugust 

so the question is how to split the payments - if we pay for the first batch not 9$ - 10 $ - 1$ shoud be moved to the prepayment for the next batch - not to the 70% after the  180 day after shippment day  

Shoud i need add an id of payment or id the shipment in contract ? 

If anyone can help me with this, it would be very greatly apprecaited.

Thanks in advance!

[collinq]

Hi @Stanislavp ,

Does your data have the amount of each payment that is due and do you ahve shipment information for each shipment as well?

The answer to the question probably depends on how the data is arranged in your data set.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[Stanislavp]

Hi @collinq 

Thank you very much for the fast answer.

I have the total amount of the contract  ( 100$ ) in one contract can be one  two or more product.

Then the supplier tell us what and then he plan to ship ( we have a planned date and the fact ). 

So after that we know the sum of payment.

now im creating the model so i can add any data what i need to mark the payments or for contract for collecting fo power BI.  

[collinq]

Hi @Stanislavp ,

If you can make the dataset then I would do something like this:

Payment Amounts

Then, the data would like somewhat like this when you get information:

Payment Amount Sample Data

 Then, you can either use column formulas, or more likely, measures that would calculate total amount of contract vs paid amounts and planned dates versus actual dates and total paid to date and so on and so on.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[Stanislavp]

Dear @collinq 

I made a table with my example 

Here is two batches, for the first one we need to make prepayment 9$ - 30% ( according the terms of contract ) 

But we paid 10$ so 9 $ will be as a prepayment- so no debt - we well have debt only after 180 days after fact ship date

1 $ need to move as a prepayment to the second batch - so we need pay less 

The question is now to mark the payments for power BI ? Or maybe we can pay less , so we have the debt. 

Shoud i mark each shipment with ID to collect the payments or shoud i mark the paymet with ID.

Thanks in advance