Solved: Re: cash flow calculation

Anonymous · ‎06-03-2019

Hello people
I am building a cash flow dashboard and have some questions on how to build a measure.

My data model consists of 3 tables

Invoices, Calendar and Bank Balance

In the Invoices table I have the following structure:

Date (DD / MM / YYYY), Invoice, Type, Value
02/06/2019, 9, PAYMENT, 555
03/06/2019, 1, RECEIPT, 1000
03/06/2019, 2, PAYMENT, 2000
04/06/2019, 3, RECEIPT, 1500
04/06/2019, 4, PAYMENT, 2500
05/06/2019, 5, RECEIPT, 100
05/06/2019, 6, PAYMENT, 200
05/06/2019, 7, RECEIPT, 500
06/06/2019, 8, PAYMENT, 200

From this information I create 2 measures, adding the "Value" by "Type", thus creating entrys and exits.

In the Bank Balance table I have the following structure:

Date (DD / MM / YYYY), Bank Balance
02/06/2019, 2555
03/06/2019, 2000

In the Calendar table I have the following structure:

Date (DD / MM / YYYY)
31/05/2019
01/06/2019
02/06/2019
03/06/2019
04/06/2019
05/06/2019
06/06/2019

From this table and the Calendar I create a measure called "Opening Balance", which is the sum of the minimum date in the selection of the "Date" field of the Calendar table.

Opening Balance =
VAR MinDateFromSelectedInterval = MIN (Calendar [Date])
VAR MinAvailableDateWithValues = CALCULATE (MIN ('Bank Balance' [Date]), 'Bank Balance' [Date]> = MinDateFromSelectedInterval)
RETURN
CALCULATE (SUM ('Bank Balance'); FILTER ('Bank Balance'; 'Bank Balance' [Date] = MinAvailableDateWithValues))

And to finish the last measure I create is the "Final Balance" which is the sum of the 3 above.

My problem is when I need to create the "Opening Balance", from dates that I have not yet registered in the table "Bank Balance" example:

02/06/2019
Opening Balance 2555
Entrys 0
Exits 555
Final Balance 2000

03/06/2019
Opening Balance 2000
Entrys 1000
Exits 2000
Final Balance 1000

04/06/2019 (This date I do not have the amount registered in the bank balance table, I need to bring the "Final Balance" from the previous day)

Opening Balance 0 (Here the value should be 1000)
Entrys 1500
Exits 2500
Final Balance -1000 (Here the value should be 0)

Can someone help me?
Thank you

Anonymous · ‎06-27-2019

Hi @Anonymous ,

You can use below measure formula to calculate rolling balance based on calendar and invoice:

measure =
VAR curr =
    MAX ( 'Calendar'[Date] )
VAR _fBalance =
    CALCULATE (
        SUM ( 'Bank Balance'[Bank Balance] ),
        FILTER (
            ALL ( 'Bank Balance' ),
            [Date] = MINX ( ALL ( 'Bank Balance'[Date] ), [Date] )
        )
    )
RETURN
    IF (
        curr IN VALUES ( Invoices[Date] ),
        SUMX (
            FILTER ( ALL ( Invoices ), [Date] <= curr ),
            [Value_Payment] + [Value_Receipt]
        ) + _fBalance
    )

Regards,

Xiaoxin Sheng

View solution in original post

Anonymous · ‎06-13-2019

Anyone can help me ?

@Anonymous wrote:
Hello people
I am building a cash flow dashboard and have some questions on how to build a measure.

My data model consists of 3 tables
Invoices, Calendar and Bank Balance

In the Invoices table I have the following structure:

Date (DD / MM / YYYY), Invoice, Type, Value
02/06/2019, 9, PAYMENT, 555
03/06/2019, 1, RECEIPT, 1000
03/06/2019, 2, PAYMENT, 2000
04/06/2019, 3, RECEIPT, 1500
04/06/2019, 4, PAYMENT, 2500
05/06/2019, 5, RECEIPT, 100
05/06/2019, 6, PAYMENT, 200
05/06/2019, 7, RECEIPT, 500
06/06/2019, 8, PAYMENT, 200

From this information I create 2 measures, adding the "Value" by "Type", thus creating entrys and exits.

In the Bank Balance table I have the following structure:

Date (DD / MM / YYYY), Bank Balance
02/06/2019, 2555
03/06/2019, 2000

In the Calendar table I have the following structure:

Date (DD / MM / YYYY)
31/05/2019
01/06/2019
02/06/2019
03/06/2019
04/06/2019
05/06/2019
06/06/2019

From this table and the Calendar I create a measure called "Opening Balance", which is the sum of the minimum date in the selection of the "Date" field of the Calendar table.

Opening Balance =
VAR MinDateFromSelectedInterval = MIN (Calendar [Date])
VAR MinAvailableDateWithValues = CALCULATE (MIN ('Bank Balance' [Date]), 'Bank Balance' [Date]> = MinDateFromSelectedInterval)
RETURN
CALCULATE (SUM ('Bank Balance'); FILTER ('Bank Balance'; 'Bank Balance' [Date] = MinAvailableDateWithValues))

And to finish the last measure I create is the "Final Balance" which is the sum of the 3 above.

My problem is when I need to create the "Opening Balance", from dates that I have not yet registered in the table "Bank Balance" example:

02/06/2019
Opening Balance 2555
Entrys 0
Exits 555
Final Balance 2000

03/06/2019
Opening Balance 2000
Entrys 1000
Exits 2000
Final Balance 1000

04/06/2019 (This date I do not have the amount registered in the bank balance table, I need to bring the "Final Balance" from the previous day)
Opening Balance 0 (Here the value should be 1000)
Entrys 1500
Exits 2500
Final Balance -1000 (Here the value should be 0)

Can someone help me?
Thank you

Anonymous · ‎06-27-2019

Hi @Anonymous ,

You can use below measure formula to calculate rolling balance based on calendar and invoice:

measure =
VAR curr =
    MAX ( 'Calendar'[Date] )
VAR _fBalance =
    CALCULATE (
        SUM ( 'Bank Balance'[Bank Balance] ),
        FILTER (
            ALL ( 'Bank Balance' ),
            [Date] = MINX ( ALL ( 'Bank Balance'[Date] ), [Date] )
        )
    )
RETURN
    IF (
        curr IN VALUES ( Invoices[Date] ),
        SUMX (
            FILTER ( ALL ( Invoices ), [Date] <= curr ),
            [Value_Payment] + [Value_Receipt]
        ) + _fBalance
    )

Regards,

Xiaoxin Sheng

Anonymous · ‎06-04-2019

Hi @Anonymous ,

Can you please share a pbix file with some sample data for test? It is hard to test and coding formula without any sample data.

Notice: do mask on sensitive data before sharing.

Regards,

Xiaoxin Sheng

Anonymous · ‎06-13-2019

Hi Xiaoxin Sheng,

Here is the attached file and the photo where my problem is.
See that I need the "Opening Balance" to always appear independent if there was no "Bank Balance" registered

Download PBIX FILE

opening problem.png