Cashflow with opening and and balance in Power BI

[Anonymous]

Hello, I'm new to Power BI. I want to make Cashflow in Power BI. I have some expenses and revenue by date in Power BI - matrix report. It look like this:

I have only one table - vmCashPaymentOrders and two important fields: Due_Date and Flow_BGN.

I have put the date in the columns and data in the rows.  I want the following thing:

day by day to see previous day sum(end balance) of filed  Flow_BGN as opening balance for the next day.

 

I will try to explain simpler with an excel example:

Thanks in advance!

[v-sihou-msft · Accepted solution]

@Anonymous

In Power BI Desktop, it's not supported to directly edit opening/closing Total. If you want to display a matrix as above screenshot, you need to add OpeningBalance and EndingBalance row within each Date group. So your source data can be like:

Now you need to add an index column in Query Editor first. 

Then you can create a calculated column like below:

Flow =
IF (
    Table2[Flow_BGN] = "Revenue"
        || Table2[Flow_BGN] = "Expense",
    Table2[Amount],
    CALCULATE (
        SUM ( Table2[Amount] ),
        FILTER ( ALL ( Table2 ), Table2[Index] <= EARLIER ( Table2[Index] ) )
    )
)

You can put Date as Column group, Flow_BGN as Row group, and put above column into Value in your matrix.

Regards,

[Anonymous]

I think I understood most of the description and i also have tried with a new table from excel with all needed rows:

This is the table:

This is the Matrix view, showing right calculations:

Everything looks ok except: How to add OpeningBalance and EndingBalance row within each Date group?

Have in mind that the table vmCashPaymentOrders is from SQL Source(MS SQL), so i don't have this columns by default. The table contains only Revesues and Expenses, Date, Amount and some other not important columns:

Thanks!

[igorabdo]

Hi everybody

Anybody did this?

About initial balance cash flow?

I've the same problem.