Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

cash flow calculation

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 ta...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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