Closing and opening balance with cashflow

[Planda]

Hi,

I am having quite a big trouble of calculating cash flow in my report.

I have this situation:

	2016-01	2016-02	2016-03	2016-04	2016-05	2016-06	2016-07	2016-08	2016-09	2016-10	2016-11	2016-12
Opening cash balance	2583000	2092389	1507276	766955	156132	3024054	2294112	1601529	703235	220532	4112775	3174260
Cashflow	-490611	-585113	-740321	-610823	2867922	-729942	-692583	-898294	-482703	3892243	-938515	-926028
Closing cash balance	2092389	1507276	766955	156132	3024054	2294112	1601529	703235	220532	4112775	3174260	2248232

I have a static number of 2583000 on January 2016 as an opening cash balance. I have a measure created from Cashflow, that is calculated from accounting book table.  

In theory I should have these two measures:

   1) Closing cash balance = Opening cash balance + Cashflow

   2) Opening cash balance = CALCULATE([Closing cash balance],PREVIOUSMONTH('Date'[Date]))

But I am getting circular dependency error. It is understandable, but I can't figure out how to make this situation work.

Anyone has experience with this and can suggest some solution?

Thank you

[v-robertq-msft · Accepted solution]

Hi, @Planda 

According to your description, I think you should first do some transformation on the data then create measures to achieve this, you follow my steps:

1. You can refer to the document that amitchandak posted to transpose this data in Power query editor, then get the data like this(If you don’t understand, you can learn from the pbix file I posted below):

2. Create a calculated column:

Month = MONTH('Table'[Date])

3. Create two measures:

Closing cash balance =

var _cashflow=CALCULATE(SUM('Table'[Cashflow]),FILTER(ALL('Table'),[Month]<=MAX([Month])))

return

2583000+_cashflow

Opening cash =

var _value=CALCULATE([Closing cash balance],FILTER(ALL('Table'),[Month]=MAX([Month])-1))

return

IF(MAX('Table'[Month])=1,2583000,_value)

4. Create a table chart and place columns and measures like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Planda , first of all you need to transpose this data

Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

pivot/unpivot - https://radacad.com/pivot-and-unpivot-with-power-bi

If need conver month to date

Date = Date(left([Month],4), right([month],2),1)

You can use date in place of month.

Create a measure like

Closing cash balance =
var _min = minx(all(Table), Table[Month]) //or use date
return
calculate(sum(Table[Opening cash balance]), filter(Table, Table[Month] =_min)) - calculate(sum(Table[Cashflow]), filter(Table, Table[Month] <=max(Table[Month)))