Count function with an expression

[Deva002]

Hi All,

I came across a very simple - complex (pardox) situation. I have a table like the below one with Emp_ID, Joining Date & Relieving Date with 5000 employee records for the last 20 years. I need to get a Monthly headcount report where a person has to be joined before the selected month and not relieved that month.

Emp_ID	Joining Date	Relieving Date

Deva

[Eric_Zhang · Accepted solution]

@Deva002

You need a calendar table and a measure like

headcount = 
COUNTAX (
    FILTER (
        ALL ( Employees ),
        Employees[Joining Date] <= MAX ( CalendarTbl[MonthEnd] )
            && (
                Employees[Relieving Date] > MAX ( CalendarTbl[MonthEnd] )
                    || ISBLANK ( Employees[Relieving Date] )
            )
    ),
    Employees[Emp_ID]
)

Dataset

C

[KGrice]

Hi @Deva002. Could you provide some clarification with an example? If Emp_ID 123 has a joining date of 1 June 2016 and a relieving date of 1 August 2016, they would contribute to the headcount for only the month of July 2016. However, if they joined any time in May 2016, they should contribute to the headcount for June and July 2016. Is that accurate?