Weekly opening balance

[Oomsen]

For my cash forecast i would like to create weekly overview (see attached).

Week 44 is working correct, but after that i would like to see that the closing balance of week 44 is de opening balance in week 45, etc. 

I'm currently facing circularity. 

[lc_finance · Accepted solution]

Hi @Oomsen ,

you are right. There was one part of the calculation that I had not fixed.

You can download the new version from here.

I rechecked calculations manually, now all seem to be good.

Let me know if it also works for you!

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[lc_finance]

Hi @Oomsen ,

I do not see the file you attached.

Can you include your Power BI sample again? (for example, uploading it to OneDrive and the including a link to it)

That will help in better understanding your current formulas

Regards

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Oomsen]

@lc_finance this is the first time i share via OneDrive, i hope it will work.

.PBIX 

[lc_finance]

Hi @Oomsen ,

I looked at your file and you can find my proposed solution attached.

I created a new measure for opening that does exactly what you mentioned: take the closing for the prior week.

Here is the DAX code for it:

opening new = 
CALCULATE([closing],'Dimdate'[Weeknummer]=VALUES('Dimdate'[Weeknummer])-1)

And here is a screenshot:

Is this what you are looking for?

Let me know if you need more help

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Oomsen]

@lc_finance thanks for your solution. Unfortunately it's not completely what i'm looking for. 

I would like to forecast my cashflow. 

I want to see the opening balance of the week (opening), plus the transactions (mutatie), plus the debtors (deb), minus creditors (cred) is closing balance (closing).

Then next week i would like to start with the closing of last week and again, plus transactions, plus debtors and minus creditors. 

I'm expecting:

Week          Opening        Mutatie          Deb           Cred             Closing

45               31240             0                    440           889               30790 

46               30790             0                    386           -97                31273

47               31273             0                    992           961                31304       

[lc_finance]

Hi @Oomsen ,

You can find attached an updated version.

I obtain different numbers than you, but I rechecked them in Excel and they look correct.

week	opening new	mutatie	deb	cred	closing new	check closing
53
52				0		0
51	-405			0	-405	0
50				-405	-405	0
49				0		0
48	31.26			0	31.26	0
47	482.79		992	-960.78	514.05	-0.04
46	-449.48		386	96.56	33.31	-0.23
45	40332.06		440	-889.32	39882.58	0.16
44	31272.19	-32	25457	-16364.5	40332.06	0.67
43	15523.69	15749		0	31272.19	0.5
42	26763.57	-11240		0	15523.69	-0.12
41	3532.45	23231		0	26763.57	-0.12
40		3532		0	3532.45	-0.45

Let me know if this fixes your problem!

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Oomsen]

@lc_finance  it's still not completely what i'm looking for. 

I'm expecting below:

46      39882.58    0      386      96.56    40172.02

47      40172.02    0      992     -960.78  42124.80

48      42124.80    0      0          0           42124.80

[lc_finance · Accepted solution]

Hi @Oomsen ,

you are right. There was one part of the calculation that I had not fixed.

You can download the new version from here.

I rechecked calculations manually, now all seem to be good.

Let me know if it also works for you!

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Oomsen]

@lc_finance although it is now working properly, I noticed that there is a difference with the actual balance. This is because last year's balance is not included. How could I add this?

[lc_finance]

Hi @Oomsen 

I reopened the file and I see that you have a year slicer. Thus if you choose 2019 on the year slicer, all the amounts from 2018 and 2020 will be filtered out.

There is a solution to it, which is using ALL(). This function, along with CALCULATE, allows you to remove filters for a certain formula.

The syntax would be:

CALCULATE( your calculation goes here, ALL(Dimdate[Jaar]))

the calculation you enter will disregard the slicer on the year, it will thus be able to access all years.

Does this help you?

Regards

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[Oomsen]

@lc_finance i changed the measure to: 

opening = CALCULATE(SUM(Cashflow[mutatie])+[deb]-[cred];'Dimdate'[Weeknummer]<VALUES('Dimdate'[Weeknummer]);ALL(Dimdate[Jaar]))

The result is below:

[lc_finance]

Hi @Oomsen ,

is that the result you were looking for?

LC

[Oomsen]

@lc_finance no it's not. 

The closing balance at 201 was -46k. 

It looks like it's making a sum of all years after the addition of ALL.

[lc_finance]

Hi @Oomsen ,

I see that in the Power BI I have, the Transactions table only has data for the year 2019. Can you share a sample of your Power BI with data in multiple years?

That will help me in understanding the issue.

Regards,

LC

[Oomsen]

@lc_finance unfortunately i'm not able to share the complete .pbix .

The problem is that "Dimdate'[Week Nummer]<VALUES('Dimdate'[Week Nummer])" doesn't work for the first week every year. 

It gives a blank on week 1 every year. 

[lc_finance]

Hi @Oomsen ,

I understand that you cannot share it for confidentiality reasons.

But maybe it's possible to share a sample of it? A sample of it with the problem

Seeing the file would make it easier to help you find the issue.

Regards

LC

[Oomsen]

@lc_finance  below my test file. 

The "opening" measure isn't running if you ask me.

In my report i would like to see visuals between today and the next 6 months. 

A relative date filter would be the best solution for this i guess?

.PBIX 

[Oomsen · Accepted solution]

@lc_finance  do you have any suggestion?