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

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

.PBIX 

  • 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       

      • lc_finance's avatar
        lc_finance
        Solution Sage
        6 years ago

        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.

         

        weekopening newmutatiedebcredclosing newcheck closing
        53      
        52   0 0
        51-405  0-4050
        50   -405-4050
        49   0 0
        4831.26  031.260
        47482.79 992-960.78514.05-0.04
        46-449.48 38696.5633.31-0.23
        4540332.06 440-889.3239882.580.16
        4431272.19-3225457-16364.540332.060.67
        4315523.6915749 031272.190.5
        4226763.57-11240 015523.69-0.12
        413532.4523231 026763.57-0.12
        40 3532 03532.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