topic Re: Counting employees on vecation in DAX Commands and Tips

Counting employees on vecation

nikostou — Fri, 24 Jun 2022 07:35:48 GMT

Hi All

I have a table with employee names and vacation periods.

One name can be mentioned several times in the name column, as the employee can have several vacation periods. It is possible for me to add a uniqe "ScheduleNr" if I need a uniqe identifier for the vacation period.

In my table I have a begin date and an end date.

I would like some kind of solution where I can count how many employees will be vacating in one specific week.

It doesent matter if the employee has one, three og seven vacation days in that week. I just want to know how many employees will be on vacation for any part of that week..

Does anyone have any ideas for this?

Will be looking forward to hearing from you! Thanks in advance 🙂

Re: Counting employees on vecation

tamerj1 — Fri, 24 Jun 2022 09:13:27 GMT

Hi nikostou
You can create a new calculated column

ScheduleNr = VAR CurrentName = TableName[Name] VAR CurrentEmpTable = CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Name] ) ) VAR Ranking = RANKX ( CurrentEmpTable, TableName[BiginDate],, ASC ) RETURN CurrentName & "-" & Ranking

Then in a measure you can return the count

Count = COUNT ( TableName[ScheduleNr] )

Re: Counting employees on vecation

nikostou — Fri, 24 Jun 2022 10:57:08 GMT

Hi tamerj1

I think you misunderstand my challenge.

I already have a ScheduleNr.
I am not interested in counting the number og vacation periods.

I am interested in counting the number of employees that is vacating in week 28, for instance.

Re: Counting employees on vecation

v-chenwuz-msft — Wed, 29 Jun 2022 06:45:16 GMT

Hi nikostou ,

A calendar table is a must, which is used for user to select the week. Then create a measure named "count" to count the number of employee.

Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), FILTER ( 'Table', OR ( [BeginDate] >= MIN ( 'Calendar'[Date] ) && [BeginDate] <= MAX ( 'Calendar'[Date] ), [EndDate] >= MIN ( 'Calendar'[Date] ) && [EndDate] <= MAX ( 'Calendar'[Date] ) ) ) )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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