Forum Discussion
Helper IIHow to Calculate Active Employees based on Rolling Status
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,
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]
1 Reply
- amitchandak
Super User
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]