cancel
Showing results for
Did you mean:
Frequent Visitor

## calculate instances of Sick leave for employee after overtime

Hi,

I would really appreciate some help please. I have Rostering table with Employee, Date and Work Type. Work Type, among others, can be Work, Overtime, Sick or Rostered off. I am trying to calulate for each employee, a count of the number of instances an employee takes a sick day in the day after overtime and a separate measure for 2 days after etc. It is possible for an employee to have Work and Overtime on the same day. The data looks as follows:

 Employee Date Work Type Employee1 1/02/2019 Work Employee1 2/02/2019 Work Employee1 2/02/2019 Overtime Employee1 3/02/2019 Sick Employee1 4/02/2019 Sick Employee1 5/02/2019 Work Employee1 5/02/2019 Rostered off Employee2 1/02/2019 Work Employee2 2/02/2019 Overtime Employee2 3/02/2019 Work Employee2 4/02/2019 Sick Employee2 5/02/2019 Work

Output would be as follows:

 Employee Sick occur after 1 day Employee1 1.00 Employee2 -

 Employee Sick occur after 2 days Employee1 1.00 Employee2 1.00

Can someone assist with a DAX solution please? I just can't seem to get it working.

cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: calculate instances of Sick leave for employee after overtime

You may create two columns and two measures to get it.For example:

```Next1day_Worktype =
CALCULATE (
MAX ( Table1[Work Type] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Date]
= EARLIER ( Table1[Date] ) + 1
)
)
```
`Next2day_Worktype = CALCULATE(MAX(Table1[Work Type]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Date]=EARLIER(Table1[Date])+2 ))`

Measures:

```Sick occur after 1 day =
CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next1day_Worktype]="Sick")))```
```Sick occur after 2 day =
CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next2day_Worktype]="Sick")))```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team

## Re: calculate instances of Sick leave for employee after overtime

You may create 2 measures like below:

```Sick occur after 1 day =
VAR overtime_date =
CALCULATE (
MIN ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Work Type] = "Overtime"
)
)
RETURN
CALCULATE (
COUNT ( Table1[Employee] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Date] = overtime_date + 1
&& Table1[Work Type] = "Sick"
)
)
```
```Sick occur after 2 day =
VAR overtime_date =
CALCULATE (
MIN ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Work Type] = "Overtime"
)
)
RETURN
CALCULATE (
COUNT ( Table1[Employee] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Date] = overtime_date + 2
&& Table1[Work Type] = "Sick"
)
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: calculate instances of Sick leave for employee after overtime

Hi Cherie,

Thanks so much for the reply. I have implemented the code as supplied but unfortunately it is only calculating a value of 1 per employee (I have examples where the occurrence is > 1)  and ommitted at least 1 employee that I can see. Are you able to assist please?

cheers

Emil

Community Support Team

## Re: calculate instances of Sick leave for employee after overtime

Could you update the sample data to show me the difference(where the occurrence is > 1)?I cannot reproduce your scenario.Maybe you may try to use DISTINCTCOUNT as below.

```Sick occur after 1 day =
VAR overtime_date =
CALCULATE (
MIN ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Work Type] = "Overtime"
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Table1[Employee] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Date] = overtime_date + 1
&& Table1[Work Type] = "Sick"
)
)
```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: calculate instances of Sick leave for employee after overtime

Thanks again. The following test file replicates the issue:

 Employee Date Work Type Employee1 1/02/2019 Work Employee1 2/02/2019 Work Employee1 2/02/2019 Overtime Employee1 3/02/2019 Sick Employee1 4/02/2019 Sick Employee1 5/02/2019 Work Employee1 5/02/2019 Rostered off Employee2 1/02/2019 Work Employee2 2/02/2019 Overtime Employee2 3/02/2019 Work Employee2 4/02/2019 Sick Employee2 5/02/2019 Work Employee1 6/02/2019 Work Employee1 7/02/2019 Overtime Employee1 8/02/2019 Sick Employee1 9/02/2019 Sick Employee1 10/02/2019 Work Employee1 11/02/2019 Rostered off employee3 29/06/2018 Overtime employee3 30/06/2018 Overtime employee3 1/07/2018 Sick employee3 2/07/2018 Sick

Employee 1 should have a cont of 2. Employee 3 is not reported at all

Community Support Team

## Re: calculate instances of Sick leave for employee after overtime

You may create two columns and two measures to get it.For example:

```Next1day_Worktype =
CALCULATE (
MAX ( Table1[Work Type] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Employee] ),
Table1[Date]
= EARLIER ( Table1[Date] ) + 1
)
)
```
`Next2day_Worktype = CALCULATE(MAX(Table1[Work Type]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Date]=EARLIER(Table1[Date])+2 ))`

Measures:

```Sick occur after 1 day =
CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next1day_Worktype]="Sick")))```
```Sick occur after 2 day =
CALCULATE(COUNTROWS(FILTER(Table1,Table1[Work Type]="Overtime"&&Table1[Next2day_Worktype]="Sick")))```

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: calculate instances of Sick leave for employee after overtime

I really appreciate your assistance on this! It works as intended