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

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