Calculate the Average Headcount in a period

[TomLU123]

Dear Experts, 

I have a dataset like below. The database will refresh the active employee ID in following table once a day. The "Report Date" is the refresh date. 

For example, on 6/1/2018 and 6/2/2018, they are empty which means there are no employees.

On 6/3/2018, there are 4 employees joined.

On 6/4/2018, there are 4 employeess (no one join or left).

On 6/5/2018, there are 3 employees (one left).

I sitll wish to create a to create a measure to calculate the Average Headcount of the period by Sumimg the not empty headcount of the erlist date and not empty headcount of the latest date's the user choose, and then divided by 2.

For example, if the user choose 6/1/2018 to 6/5/2018, the Average Headcount of the period= (4+3)/2.

If the user choose 6/3/2018 to 6/4/2018, the Average Headcount of the period = (4+4)/2

Report Date	Employee ID
6/1/2018
6/2/2018
6/3/2018	111
6/3/2018	222
6/3/2018	333
6/3/2018	444
6/4/2018	111
6/4/2018	222
6/4/2018	333
6/4/2018	444
6/5/2018	111
6/5/2018	222
6/5/2018	333

How should we write the expression to achieve that?

Many thanks!

Best regards,

Tom

[Ashish_Mathur]

Hi,

If the earliest date is 6/1/2018, then how will one compute the "non empty headcount of the earliest date"?

[SteveCampbell · Accepted solution]

You can try:

Average Headcount  = 
VAR _earliest_date = CALCULATE(MIN(Table[Report Date]),Table[Employee ID]<>BLANK())
VAR _latest_date = CALCULATE(MAX(Table[Report Date]),Table[Employee ID]<>BLANK())

RETURN

DIVIDE(
     COUNTROWS(FILTER(Table,Table[Report Date] = _earliest_date) )
   + COUNTROWS(FILTER(Table,Table[Report Date] = _latest_date) )
 ,
     2
)

and replace Table with the name of your table.