Use a measure 'Active Unique Headcount' using SUMX function.

[lnayak]

HeadCount = VAR selectedDate=

MAX('Calendar'[Date]

RETURN

SUMX('EDE'[Worker #],

VAR WorkerStartDate = ('EDE'[MinStartDate])

VAR WorkerEndDate = ('EDE'[MaxEndDate])

RETURN IF ( WorkerStartDate<=selectedDate && OR (WorkerEndDate>=selectedDate, WorkerEndDate=BLANK(),1,0))

Used this function but am not getting the result.
Please help!

Thanks in advance

[v-yetao1-msft · Accepted solution]

Hi @lnayak 

Based on your formula, I created a simple sample.

Original data :

Then use the formula you provided . I modified it to return the value 1 or 0 .

HeadCount =
VAR selectedDate=MAX('Calendar'[Date])
VAR _WorkerStartDate= SELECTEDVALUE(EDE[MinStartDate])
VAR _WorkerEndDate = SELECTEDVALUE(EDE[MaxEndDate])
RETURN IF ( _WorkerStartDate<=selectedDate && _WorkerEndDate>=selectedDate,1,0)

Then create a measure to count the total value .

count = SUMX('EDE',[HeadCount])

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lnayak]

Hi,

Let me explain what am looking for am trying to work on headcount formula and if sumx doesn't work i need to change it to distinct count. can someone help me out here please?

[lnayak]

Hi Tamerj1,

The date filter in report would just be from the calendar.

[tamerj1]

Hi @lnayak 
Yes

[tamerj1]

Hi @lnayak 

the first argument of SUMX is a table not a column. Besides no need to store column value in a variable just start with IF directly. But do you have to use SUMX?

[lnayak]

Hi @tamerj1 

Yes, I used table within SUMX. Yes, i want to use this function to know if i get the result.

Thanks,

Laxmi 

[tamerj1]

You should if there is no other date filter on your visual