cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deva002 Frequent Visitor
Frequent Visitor

Count function with an expression

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_IDJoining DateRelieving Date
   
   

 

Deva

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Count function with an expression

@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

Capture.PNG

 

Capture.PNG

Capture.PNG

 

 

C

2 REPLIES 2
KGrice Established Member
Established Member

Re: Count function with an expression

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?

Moderator Eric_Zhang
Moderator

Re: Count function with an expression

@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

Capture.PNG

 

Capture.PNG

Capture.PNG

 

 

C