Scheduling payments based on different payment terms

[sarpcastic]

Hello everyone, I'm new to Power BI, currently trying to create a table for cashflow with the below two tables;

Table 1:

This table's first collumn is not in the screenshot; It is the name of the product we are planning to purchase,
Sum of Total Value; is the total value of the planned purchasement
Paybasedate; is the base date for the payment schedule
Payment Term; these terms are defined in the next table

Table2: 
This is the unpivoted version of the payment terms table

 For example let's say I have product A in Table 1, with sum of total value of 200.000, 01.06.2024 in paybase date and "Fruit" as pay term (you can see Fruit pay term details in the second table), that means I need to schedule payments ;

120.000 (60%) in 01.06.2024 (+0 days to pay base date)
20.000 (10%)  in 01.07.2024 (+30 days to paybasedate)
20.000 (10%)  in 01.08.2024

20.000 (10%)  in 01.09.2024

20.000 (10%)  in 01.10.2024

Do you have any suggestions on how to create such a table? Thank you.

[DataNinja777 · Accepted solution]

Hi @sarpcastic ,

Your 1st screen print only shows the cash flow scenarios where only one cash flow occurs for particular transactions and does not include the payment terms such as "BlackCarrot", "30+70 in 30", "30+70 in 60" and "Fruit" where multiple cash flows are triggered by given transactions.   In order to fully demonstrate the interesting technical topic specific to your case, I've randomly modified your 1st screen print to fully cover the payment terms which trigger multiple cash flow timings.  Yellow highlighted payment terms were modified from your original 1st screen print.  

I've named the 1st table 'Transaction' and 2nd table in your screen shot as 'Payment term'.  

With this preamble, I suppose there are multiple ways to achive your required output and one of them is as shown below:

First create an explicit measure of the values:

Value = sum('Transaction'[Sum of Total Value])

Then create another explicit measure of the PaymentPercent.  

Payment % = sum('Payment term'[PaymentPercent])

Then create allocation measures for each of the payment terms as shown below:

For payment term of 20 days,

Value (20 Days) =
CALCULATE (
    SUMX (
        'Transaction',
        IF (
            'Transaction'[Payment Term] = "20 Days",
            [Value]
                * CALCULATE ( [Payment %], KEEPFILTERS ( 'Payment term'[Type] = "20 Days" ) )
        )
    )
)

For payment term of 30 days,

Value (30 Days) =
SUMX (
    'Transaction',
    IF (
        'Transaction'[Payment Term] = "30 Days",
        [Value]
            * CALCULATE ( [Payment %], KEEPFILTERS ( 'Payment term'[Type] = "30 Days" ) )
    )
)

For payment term of "30+70 in 30",

Value (30+70 in 30) =
SUMX (
    'Transaction',
    IF (
        'Transaction'[Payment Term] = "30+70 in 30",
        [Value]
            * CALCULATE ( [Payment %], KEEPFILTERS ( 'Payment term'[Type] = "30+70 in 30" ) )
    )
)

For payment term of "30+70 in 60",

Value (30+70 in 60) =
SUMX (
    'Transaction',
    IF (
        'Transaction'[Payment Term] = "30+70 in 60",
        [Value]
            * CALCULATE ( [Payment %], KEEPFILTERS ( 'Payment term'[Type] = "30+70 in 60" ) )
    )
)

Then repeat this for the remainder of the yellow highlighted payment terms.  (I suppose this is the reason there is calculation group, but please excuse me to do this in an old fashioned way.)

Then sum the payment term measures thus created in one combined measure like below:

Payment terms applied = 
[Value (20 Days)]+
[Value (30 Days)]+
[Value (45 Days)]+
[Value (60 Days)]+
[Value (90 Days)]+
[Value (L/C)]+
[Value (30+70 in 30)]+
[Value (30+70 in 60)]+
[Value (BlackCarrot)]+
[Value (Cash)]+
[Value (Fruit)]

Resultant output is as shown below, where the top matrix table shows the individual measures on rows and the bottom matrix table shows the combined measure and payment type field on the rows and we can confirm that they are yielding the same output.  

Adding payment timing field on row shows additional granularity available in the allocated output.  

For the final output, we need to show the cash flow timing as dates which can be filtered using the same date field from the calendar table like the transaction dates. 

As a step towards this goal, I created a calendar table and linked it first with the transaction table date field.  However, this will only show the date of transaction instead of the date of cash flow according to the payment terms.

As the next step, I created a calculated table and visualized the table in the table view:

Cash flow timing =
SUMMARIZECOLUMNS (
    'Transaction'[Payment Term],
    'Calendar'[Date],
    'Payment term'[MaturityDays],
    "Payment terms applied", [Payment terms applied]
)

Table visualization like below makes it easy to see the output produced by dax.  

Then next, I simply summed up the numerical column.  

Cash flow by timing = sum('Cash flow timing'[Payment terms applied])

In order to show side by side, using the date field the transaction amount by transaction date and by the cash flow date, I tried to create the relationship between the calendar table and payment date field in the 'Cash flow timing' table.  However, as I got the circular dependency error, as a workaround, I created another calendar table and called 'Calendar table' as opposed to the first 'Calendar', and hid the first calendar table to avoid confusion in visualization.   The final data model looks like below:

Using the date field from the visible calendar table, the final output comparing the transaction date and cash flow timing date for the transaction value for each payment term looks like below.  I renamed the measures just for this visual for simplicity and conciseness purposes.  In order to easily identify which are the transaction amounts and when they are converted into cash outflows, I've highlighted transaction amounts in pink so as to distinguish them from cash outflow amounts.  

Finally, you can check that the above is correctly calculated for each payment term by looking at them by date granularity instead of yyyy-mm granularity.

It appears the calculation is correctly done.  

I attach the pbix file.  

[sarpcastic]

Thank you so much for your time and brief explanation. It worked without a problem.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...