Counting employees on vecation

[nikostou]

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 🙂 

[v-chenwuz-msft · Accepted solution]

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.

[tamerj1]

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] )

[nikostou]

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.