Power BI (DAX) - Cashflow with open and close balance

Question

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 also have table Dates, which is actually the calendar, and the column Date [Date].

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 fiеld Flow_BGN as opening balance for the next day.

I will try to explain simpler with an excel example:

Thanks in advance!

Answer 1

There is probably a better way, but maybe this will help.

I started with this table:

And ended with this table:

In the Modeling tab, starting with the first table above selected and displayed...

I created a New Measure to store the Initial Opening Balance of 1500: InitialOpeningBalance = 1500

Then I added a New Column with an Index: Index = COUNTROWS(FILTER(Table1, Table1[Due_Date]<=EARLIER(Table1[Due_Date])))

Then I added a New Column for the End Balance: End Balance = [InitialOpeningBalance] + sumx(FILTER(Table1,Table1[Index]<=EARLIER(Table1[Index])),Table1[Revenue])+sumx(FILTER(Table1,Table1[Index]<=EARLIER(Table1[Index])),Table1[Expenses])

Then I added a New Column for Opening Balance: Opening Balance = if(Table1[Index]=1,[InitialOpeningBalance],sumx(FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1),Table1[End Balance]))

Anyhow... I hope it helps.

Answer 2

I also figured out a way to do it with Power Query (Power BI's Query Editor) and get this result:

There may be a more direct (and therefore, better) method, but here's how I got it...

I started with this table as "Table1" in the Query Editor:

Then I added a column which summed the Revenue and Expenses for each row:

I added the column by clicking the "Add Column" tab and then the "Custom Column" button. In the "Add Custom Column" dialog box that popped up when I clicked the "Custom Column" button, I entered "SumRevenueExpenses" in the "New column name" text box and [Revenue]+[Expenses] in the "Custom column formula:" text box.

Then I referenced Table1...that is, I used it as a source for "Table2". That basically made a copy of Table1. I named it Table2.

To reference Table1, right-click on "Table1" at the top of the left pane of the Query Editor, and then click on Reference:

Then I added an index column by clicking the "Add Column" tab and the "Index Column" drop-down arrow and "From 1":

Then I added another custom column: I named it "End balance" and added the formula, Table.AddColumn(#"Added Index", "End balance", each 1500 + List.Accumulate(List.Range(Table1[SumRevenueExpenses],0,[Index]),0,(state,current)=> state + current)). NOTE: the 1500 in this formula is your initial 1500 Opening balance. It's hard-coded here, but you could have it in its own column, like I did with a measure in my DAX approach earlier, and reference that column instead (e.g., "...each [InitialOpeningBalance] +...")

Then I added one last custom column: I named it "Opening balance" and added the formula, Table.AddColumn(#"Added Custom1", "Opening balance", each try #"Added Custom1"{[Index]-2}[End balance] otherwise 1500). NOTE: the 1500 in this formula is your initial 1500 Opening balance. (Same comment as above, regarding the hard-coded number...you could use a column instead.)

Then I removed the "SumRevenueExpenses" and "Index" columns.

Then I dragged the "Opening balance" column leftward, till it was between the "Due_Date" and "Revenue" columns.

Then I demoted the headers by clicking the "Transform" tab and the "Use First Row as Headers" drop-down arrow and "Use Headers as First Row":

At that point, I had this table:

Then I transposed the table by clicking the "Transform" tab and then "Transpose", to get this table:

And, finally, I promoted the first row as headers by clicking the "Transform" tab and the "Use First Row as Headers" drop-down arrow and "Use First Row as Headers".

The final result is this:

Here's the query code for Table2:

let
Source = Table1,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "End balance", each 1500 + List.Accumulate(List.Range(Table1[SumRevenueExpenses],0,[Index]),0,(state,current)=> state + current)),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Opening balance", each try #"Added Custom1"{[Index]-2}[End balance] otherwise 1500),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SumRevenueExpenses", "Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Due_Date", "Opening balance", "Revenue", "Expenses", "End balance"}),
#"Demoted Headers" = Table.DemoteHeaders(#"Reordered Columns"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"

Answer 3

For the simplest answer, it is important to know if the detail numbers you are working with are balances (not addable along the time dimension) or transactions that make up balances (addable along the time dimension).

You should also know that DAX is not Excel. With DAX, totals are created with filters (usually removing them) and no cell depends on another cell to get its result. Another factor is getting a total is the filter context.

If you will always have revenues and expenses for each day (and never drill down into expenses only or revenue only, you can define what it means to be a total in DAX and what it means to be a detail line using expressions with HASONEVALUE or HASONEFILTER to check if you are on a total line or a detail line.

If you are also new to DAX, I would recommend starting with an example where you are not working with balances or giving the appearance of working with balances because while it is often the case that the data model and relationships will make much of your life easier than Excel (no VLOOKUP), there are other tasks for a beginner that are easier in Excel.

That said, either of the other solutions may provide an answer for you because they re-shape your source data outside of the data model into something more suitable for application in Power BI.

There is also a blogger, Imke Feldman, who is widely respected for her DAX insights when working with accounting numbers and you can connect to her blog here.