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 ,

 

 

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
Helper 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
    Solution 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

    • Oomsen's avatar
      Oomsen
      Helper III
      6 years ago

      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's avatar
        lc_finance
        Solution Sage
        6 years ago

        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