Hi,

I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance.  To simplify, this is what I did.

1. Dragged months to the columns labels
2. Created a slicer for selecting the Financial Year.  In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.

Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.

To determine the monthly opening cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),PREVIOUSMONTH(calendar[Date]))

To determine the monthly closing cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),calendar[Date])

Much to my surprise, I got the result as seen in the image below.  Cells B5, C6 and D5 are blank.  On going through my Bank Book, I realised that

1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31.  The last transaction was on March 28

2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31.  The last transaction was on May 30

3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.

As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.

Please also note that there can be multiple transactions on the last day of any month.  For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow).  So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day.  I have to take the final balance on that day.

Please help me solve this problem i.e. in determining the opening and closing cash balances.

Hi Ashish,

try to wrap your date filter into: ENDOFMONTH

Hi,

Please share the full formula.

Hi Ashish,

your response made me try to build it myself and now I see that it doesn't work (would have been too easy anyway :-)) - sorry.

Here we go then:

ClosingBalance=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),LASTNONBLANK(bank_book[Date], CALCULATE(SUM(bank_book[Balance])))))

OpeningBalance=CALCULATE([ClosingBalance];PREVIOUSMONTH(calendar[Date]))

The trick is to take the datefield from the FactTable in your filter argument instead of from the calendar table (just don't make it a habit :-).

Hi,

Thank you for trying but your formulas did not work either - I got the wrong result.  I tried something else but am facing a problem with one number.  Please see the file below.

http://1drv.ms/1BN2xyi

Thank you for all your help.

Ah, that file helped - thanks!

Now I can see where my understanding was wrong: I thought we need to aggregate all balances of the last day of the month which makes no sense here.

The difference between your wrong number and the desired one is actually the sum of 2 bank transactions of 28-3-2015.

Problem seems to be that there is no sign which one of the multiple transactions per day is actually the last one. As long as you dont have that, I see no chance to work with the field balance sheet. Youd have to calculate your figures by adding up all deposits and payments movements.

The other figures in your example will probably just be correct by coincidence.

If you need any help on the new calculation just drop a line.

Hi,

Thank you for your interest in solving my problem.  How else can I achieve what I want.  Please share your alternate formula.

Thank you.

Can deliver a start at least:

Movements:=SUM([Deposits])-SUM([Payments])

ClosingNew:=CALCULATE([Movements];FILTER(ALL(calendar[Date]); calendar[Date] <=MAX(calendar[Date])))+138938,48

OpeningNew:=CALCULATE([ClosingNew];DATEADD(calendar[Date];-1;MONTH))

Problem is the Balance Carried Forward (Starting Point) have hardcoded it because removing the filter context is above me.

Also check if youll always only have on row on the starting date. Otherwise youd have the old problem again: Which row to take?

BR, Imke

• Edited by Imke Feldmann Friday, June 05, 2015 10:58 AM

Hi,

Thank you for trying.  I my have multiple starting dates as well.

Then you need Power Query to prepare your data before loading it to the data model.  

This code identifies the correct Balance Carried Forward and moves it into the Deposit Column. With this preparation, youre able to use my last formulas, simply delete the hardcoded entry of the number and then everything should work fine:

let

    Source = Excel.CurrentWorkbook(){[Name="YourSourceTable"]}[Content],

    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),

    Group = Table.Group(AddIndex, {"Date"}, {{"Count", each Table.RowCount(_), type number}}),

    Sort = Table.Sort(Group,{{"Date", Order.Ascending}}),

    FirstDate = Table.FirstN(Sort,1),

    Merge = Table.NestedJoin(FirstDate,{"Date"},AddIndex,{"Date"},"NewColumn"),

    Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"Cheque number", "Date", "Purpose", "Category", "Deposits", "Payments", "Balance", "Funds spent from", "Index"}, {"Cheque number", "Date.1", "Purpose", "Category", "Deposits", "Payments", "Balance", "Funds spent from", "Index"}),

    ChgType = Table.TransformColumnTypes(Expand,{{"Balance", type number}, {"Payments", type number}, {"Deposits", type number}}),

    Repl = Table.ReplaceValue(ChgType,null,0,Replacer.ReplaceValue,{"Deposits", "Payments"}),

    AddCust = Table.AddColumn(Repl, "OB", each [Balance]+[Payments]-[Deposits]),

    Merge1 = Table.NestedJoin(AddCust,{"OB"},AddCust,{"Balance"},"NewColumn"),

    Expand1 = Table.ExpandTableColumn(Merge1, "NewColumn", {"Balance"}, {"Balance.1"}),

    Filter = Table.SelectRows(Expand1, each ([Balance.1] = null)),

    AddCust1 = Table.AddColumn(Filter, "BalanceCarriedForward", each "BCF"),

    Merge2 = Table.NestedJoin(AddIndex,{"Index"},AddCust1,{"Index"},"NewColumn"),

    Expand2 = Table.ExpandTableColumn(Merge2, "NewColumn", {"BalanceCarriedForward"}, {"BalanceCarriedForward"}),

    Repl2 = Table.ReplaceValue(Expand2,null,0,Replacer.ReplaceValue,{"Deposits", "Payments"}),

    AddCust2 = Table.AddColumn(Repl2, "Deposits_", each if [BalanceCarriedForward] = "BCF" then [Balance] else [Deposits]),

    AddCust3 = Table.AddColumn(AddCust2, "Payments_", each if [BalanceCarriedForward] ="BCF" then 0 else [Payments]),

    RemCols = Table.RemoveColumns(AddCust3,{"Deposits", "Index", "BalanceCarriedForward", "Payments"}),

    Rename = Table.RenameColumns(RemCols,{{"Deposits_", "Deposits"}, {"Payments_", "Payments"}}),

    Reorder = Table.ReorderColumns(Rename,{"Cheque number", "Date", "Purpose", "Category", "Deposits", "Payments", "Balance", "Funds spent from"})

in

    Reorder

Hi,

Thank you once again for your solution.  I adopted a different approach

1. I inserted a Serial Number column in the Bank Book
2. Here is the formula I wrote for determining the Closing Bank balance

=CALCULATE(SUM(bank_book[Balance]),FILTER(bank_book,bank_book[S. No.]=MAX(bank_book[S. No.])))

3. Here is the formula I wrote for determining the Opening balance

=[Closing Bank balance - Excel Enthusiasts]-[Total deposits]+[Total payments]

Thank you once again for helping me.