Forum Discussion

eduardomarchesi's avatar
eduardomarchesi
Regular Visitor
3 years ago

Calculating Opening / Closing Balance

Hi team,   I'm trying to use a formule like that:          Closing Balance = ( Opening Balance   + field1 + field2 )   However the "Opening Balance" is the previous value of "Closing Balance", w...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

eduardomarchesi , If this is recursive calculation like excel, that is not possible.

 

If it can be cumulative 

 

Opening = CALCULATE(SUM(Sales[Amount]),filter(all('Date'),'Date'[date] <Min ('Date'[date])))

 

Closing =  CALCULATE(SUM(Sales[Amount]),filter(all('Date'),'Date'[date] <= Max('Date'[date])))

 

Or you can consider

openingbalancemonth, openingbalancequarter, openingbalanceyear: https://youtu.be/6lzYOXI5wfo
Power BI Allocating Targets- closingbalancemonth, closingbalancequarter, closingbalanceyear:https://youtu.be/yPQ9UV37LOU

  • eduardomarchesi's avatar
    eduardomarchesi
    Regular Visitor
    3 years ago

    Hi amitchandak,

     

    Thank you very much for your help!!

     

    I've tried your suggestions but with no success.

    As you can see below, I have got the previous values of "Closing Balance" and associated with "Opening Balance", so, this Opening Balance I need incluide in the formule to "Closing Balance" as you can check in the screen from excel example below.

    PowerBI result:

    Excel example:

     

    Is it possible I send you my example and my data to take look and understand better the sctructure?

    ** I could not see the option to attach files here!! 

    Rgds

    Eduardo