How to Calculate Active Employees based on Rolling Status

[vengadesh_p]

Hi All,

I have data like below Table

I want to calculate no of Employee who are "in" Status

Expected OutPut 
A & B was "IN Status" from Mar/2021 and "Out Status from Jun/2021
So for Apr/2021 & May/2021 count is 2 because they already in "IN Status"

Data

Employee	Month Start Date	Status
A	1-Jan-2021	IN
A	1-Feb-2021	OUT
A	1-Mar-2021	IN
A	1-Jun-2021	OUT
A	1-Aug-2021	IN
B	1-Mar-2021	IN
B	1-Jun-2021	OUT
B	1-Aug-2021	IN
B	1-Sep-2021	OUT
B	1-Oct-2021	IN

Thanks,

[amitchandak · Accepted solution]

@vengadesh_p , Create a date table and join on date with this table. Use date from date table in this visual

Try measures like

emp = count(Table[Employee])

Cumm Hire = CALCULATE([emp], FILTER(ALL('Date') , 'Date'[Date] <= max('Date'[Date])), [Status] ="IN")
Cumm Termination = CALCULATE([emp], FILTER(ALL('Date'), 'Date'[Date] <= Max('Date'[Date])), [Status] = "OUT")
Current Employee = [Cumm Hire] -[Cumm Termination]