Forum Discussion
How to create actual cashflow?
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:
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,
LinkIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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.
Community SupportHi 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.
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.
first 2 calculations of start balance are correctly, but the then ending balance of march is not correct.