Forum Discussion

Oomsen's avatar
Oomsen
Icon for Helper III rankHelper 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
Icon for Solution Sage rankSolution 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
Icon for Helper III rankHelper 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
    Icon for Solution Sage rankSolution 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

    • Oomsen's avatar
      Oomsen
      Icon for Helper III rankHelper III
      6 years ago

      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's avatar
        lc_finance
        Icon for Solution Sage rankSolution Sage
        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