Cash Flow - Opening Balance and End Balance

[Anonymous]

Hello,

I have a table with payment and receipt details. Payments and receipts can be repeated, but in the table they are added up.

I have a column repeats with the first bank balance.

I would like to know, in an easy way how to get a measure for opening balance with the first value of the bank balance field. And onwards, take the final balances by adding receipts and subtracting payments. There would be two fields, one for opening balance, and ending for previous days, and the other for ending balance for each day. Example:

Below is a file with the same example mentioned in the image for a better understanding and solution.

Regards,

Bruno

[Anonymous]

Here is the file with a few tries. The problem is with more than one date, not just one date.

https://1drv.ms/u/s!Avwq0_QZmBwmg-wkuZekbiHKWE0XLg?e=uXfRG4

Regards,

Bruno

[Ashish_Mathur]

What does the Bank Balance column denote?  Is 150 (the first entry in the Bank Balance column) the opening balance?  Reshare an "easy to understand" dataset.  I will not be working off your formulas so please delete your workings.  Just let 2 tables remain - Calendar and sheet1.

[Anonymous]

The bank balance column is the opening balance. It is repeated, but I only need the value once. After filtering the period (which is the current day forward), the opening balance takes this first amount.

Follow link with clean file.

https://1drv.ms/u/s!Avwq0_QZmBwmg-wmA1VNkG1FOgIEzg?e=lbCpse

Regards,

Bruno

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Wessie]

Thanks @Ashish_Mathur. This has helped me a lot. Only in my report only the first week does not work. Only from the second week do the formulas seem to work. Do you have an idea how this can be done?

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[Wessie]

Thanks for the reply. Below are the measures I used and the result in a matrix.

Openingbalance = if(ISBLANK([Values]),BLANK(),MIN('Banksaldo'[Amount])+[Values till date])

Values = [Inkomsten]-[Uitgaven]

Values till date = CALCULATE([Values],DATESBETWEEN(Datum[Date],MINX(ALL('Datum'),'Datum'[Date]),MIN(Datum[Date])-1))

Closing balance = [openingbalance]+[Values]

The results are shown per week. The weeks are determined by a date slicer.

[Ashish_Mathur]

I cannot understand anything there.  Someone who does will help you.

[Anonymous]

It was just what I needed. Thanks a lot for the help.

[Ashish_Mathur]

You are welcome.

[Anonymous]

I sent the wrong formula. In this example the image is correct.

Regards,

Bruno

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Ashish_Mathur]

Hi,

The End Balance on 21/05/2021 should have been -400.  Likewise, the opening balance on 22/05/2021 should have been -400.  Am i correct?