cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ultrashock Regular Visitor
Regular Visitor

Cashflow with opening and and balance in Power BI

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.

 Balance.jpg

I will try to explain simpler with an excel example:

 

flow2.jpg

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Cashflow with opening and and balance in Power BI

@ultrashock

 

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:

 

2.PNG

 

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

 

4.PNG

 

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

44.PNG

 

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

 

Regards,

 

3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: Cashflow with opening and and balance in Power BI

@ultrashock

 

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:

 

2.PNG

 

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

 

4.PNG

 

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

44.PNG

 

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

 

Regards,

 

ultrashock Regular Visitor
Regular Visitor

Re: Cashflow with opening and and balance in Power BI

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:

table2.jpg

This is the Matrix view, showing right calculations:

table3.jpg

 

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:table.jpg

Thanks!

 

Highlighted
igorabdo Regular Visitor
Regular Visitor

Re: Cashflow with opening and and balance in Power BI

Hi everybody

 

Anybody did this?

About initial balance cash flow?

I've the same problem.