How to create actual cashflow?

[milex01]

Hi everybody,

I would like to create cashflow statements by using power bi. I do have some simple case that we can use to exercise this cashflow as following?

Anyone can suggest me a way of working? It can be differently than stated as below, but the purpose should be the same.

Thanks in advance!

[Anonymous · Accepted solution]

Please check example prepared on https://1drv.ms/u/s!AgAlCRkx-ILRgwn91AzYmq42ncQZ?e=pbuKCg

You need to create a date dimension table and relate this table to your purchase and sales tables. Then you can add a matrix visual to display the data as per your example. When you add the matrix table, make sure you go to Values then click the "Show on rows" toggle so that the Purchase and Sales appear as rows:

[v-xulin-mstf]

Hi @milex01,

You can try measures as:

Start balance = 
var _sum=
CALCULATE(
    MAX(PurchaseTable[Amount])+MAX(SalesTable[Amount]),
    FILTER(
       ALL(Dates),
        MONTH(Dates[Date])=MONTH(MAX(Dates[Date]))-1
    )
)
var _sum1=
CALCULATE(
    MAX(PurchaseTable[Amount])+MAX(SalesTable[Amount]),
    FILTER(
        ALL(Dates),
        MONTH(Dates[Date])=MONTH(MAX(Dates[Date]))-2
    )
)
return _sum+_sum1

Ending balance = 
CALCULATE(
    [Start balance],
    FILTER(
        ALL(Dates),
        MONTH(Dates[Date])=MONTH(MAX(Dates[Date]))-1
    )
)

Best Regards,
Link

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

[milex01]

I have applied the suggested measures and park them under purchase table as following

Below result is shown. Somehow the numbers are not quiet corresponding to the sum.

[v-xulin-mstf]

Hi @milex01,

This measure is dynamically calculated for the previous month's total.
Since there is no data before April, it is shown as empty.
You can enter more sample data for testing.

Best Regards,
Link

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

[milex01]

i have added more data from january 1st. see below result.

start and ending balance do not corresponding and the sum per column is wrond.

but when I switch the start and ending balance as following, the start and ending blance of each month are corresponding correctly, but the sum per column is still wrong.

[milex01]

first 2 calculations of start balance are correctly, but the then ending balance of march is not correct.

[milex01]

i use below data

https://milexconsultant-my.sharepoint.com/:f:/g/personal/cyzelman_milexconsultants_com/EllthWaRRX5Fr...

[v-xulin-mstf · Accepted solution]

Hi @milex01,

Please try this:

//calculated column
Ending balance = 
CALCULATE(
    SUM(PurchaseTable[Amount])+SUM(SalesTable[Amount]),
   FILTER(
       ALL(Dates),
       MONTH(Dates[Date])<=MONTH(EARLIER(PurchaseTable[Date]))))

//Measure
Start balance = 
CALCULATE(
    MAX(PurchaseTable[Ending balance]),
    FILTER(
        ALL(Dates),
        MONTH(Dates[Date])=MONTH(MAX(Dates[Date]))-1
    ))

Here is the output:

The pbix is attached.

Best Regards,
Link

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

[milex01]

Great! Thanks for all the support! I will put solve on this issue.

Just one more thing, I tried to create some fake sales and purchase in dec 2020 (to generate the right start balance in jan 2021) but then the start balance of january 2021 do not update and the complete year of 2020 filled in by random number (i think)

So to solve this issue i removed the fake sales and purchase from dec 2020 and create a new table with new balance as shown below. Not perfect but it works!!

[milex01]

done! thanks for your help

how to sum those numbers to update the starting (and ending) balance for each month as following?

[Anonymous · Accepted solution]

Please check example prepared on https://1drv.ms/u/s!AgAlCRkx-ILRgwn91AzYmq42ncQZ?e=pbuKCg

You need to create a date dimension table and relate this table to your purchase and sales tables. Then you can add a matrix visual to display the data as per your example. When you add the matrix table, make sure you go to Values then click the "Show on rows" toggle so that the Purchase and Sales appear as rows: