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 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

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

      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

  • 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