Forum Discussion

Oomsen's avatar
Oomsen
Helper III
6 years ago
Solved

Weekly opening balance

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 i...
Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    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's avatar
    Oomsen
    6 years ago

    lc_finance  do you have any suggestion?

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

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's avatar
Oomsen
Helper III
6 years ago

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