Forum Discussion

cisrael's avatar
cisrael
New Member
4 years ago

Calculate Balance

Hi all! I am wanting to calculate a balance that will include the previous period's net change (Screenshot below), except for period one. Is this possible to do?

 

 

To add a little more information to my query: 'Net Change' in Period 1 is the Period 1 Balance. What I want to do is take the following Debits - Credit total plus the previous period 'Net Change' to get my balance through period 12.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    cisrael , a measure like

    M1 = Sum(Table[Debits]) - sum(Table[Credit]) + CALCULATE(SUM(Table[Net Change]),previousmonth('Date'[Date]))

     

    Same year =

    CALCULATE(SUMX(Values('Date'[Month Year]), [M1] ),DATESYTD('Date'[Date],"12/31"))

     

    Rolling 12

    Rolling 12 = CALCULATE( SUMX(Values('Date'[Month Year]), [M1] ),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi cisrael ,

     

    Please try to create a measure like below:

    Measure = 
var Balance_ = SUM('Table'[Debit])-SUM('Table'[Credit])
var Period_ = SELECTEDVALUE('Table'[Period ID])
var Debit_ = CALCULATE(SUM('Table'[Debit]),FILTER(ALLEXCEPT('Table','Table'[Account Number]),'Table'[Period ID] = Period_ -1))
var Credit_ = CALCULATE(SUM('Table'[Credit]),FILTER(ALLEXCEPT('Table','Table'[Account Number]),'Table'[Period ID] = Period_ -1))
return Balance_+Debit_-Credit_

     


    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • cisrael6's avatar
      cisrael6
      New Member
      4 years ago

      Hi V-lianl-msft - thank you very much for your response! I have transferred the suggested measure but I am not getting the correct numbers. I'm sure it is something I am doing wrong, below is the output: