Calculate Balance

[cisrael]

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.

[V-lianl-msft]

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]

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:

[amitchandak]

@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))