DAX - for opening and closing column

[Anonymous]

Hi there

In PBI, i have a data table of Month, Batch and Exit numbers setup as below.

May I know how can I have:

-a column for Closing, which sequentially minus away (from Batch 1 to 4) the Exit number from an opening number.

-a column for Opening, which takes the last calculated Closing number.

Sample output per below.

thanks!

Month	Batch	Exit	Column - Opening	Column - Closing
Jan-18	1	100	1000	900
Jan-18	2	200	900	700
Jan-18	3	300	700	400
Jan-18	4	300	400	100

[PattemManohar · Accepted solution]

@Anonymous  Please try this as a new column

ColumnClosing = 
VAR _Sum = CALCULATE(SUM(Test194GrpRunningTotal[Exit]),ALLEXCEPT(Test194GrpRunningTotal,Test194GrpRunningTotal[Month]))
VAR _Closing = CALCULATE(SUM(Test194GrpRunningTotal[Exit]),FILTER(Test194GrpRunningTotal,Test194GrpRunningTotal[Batch]<=EARLIER(Test194GrpRunningTotal[Batch]) && Test194GrpRunningTotal[Batch]<>1))
RETURN _Sum-IF(ISBLANK(_Closing),0,_Closing)

Then add another new column for ColumnOpening as below

ColumnOpening = Test194GrpRunningTotal[ColumnClosing] + Test194GrpRunningTotal[Exit]

[Anonymous]

thank you @PattemManohar !