Calculate value of previous month using the next month data

[pperumal]

Hello,

I have a requirement, where i need to calculate the headcount for the previous months using the current month total headcount, current month new hire and current month termination

Here is the calculation,

headcount(Sep) = Headcount(OCT) - new hire(OCT) + termination(OCT)

My existing table looks like

I have done a cumulative sum on Active headcount to get the latest month total headcount of that team(=496), 

Here is the screen shot of what i need to achieve

Headcount(sep2022) = 496-2+4 => 498(Result)

Can someone help me on this!

Thank you

[ryan_mayu]

@pperumal 

pls check if this is what you want

Column = 
VAR _s=min('Table'[date])
VAR _s2=maxx(FILTER('Table','Table'[date]=_s),'Table'[active hc])
VAR _n=sumx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[new hire])
VAR _t=sumx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[termination])
return _s2+_n-_t

pls see the attachment below