cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vanschex Frequent Visitor
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:

 

EmployeeDateWork Type
Employee11/02/2019Work
Employee12/02/2019Work
Employee12/02/2019Overtime
Employee13/02/2019Sick
Employee14/02/2019Sick
Employee15/02/2019Work
Employee15/02/2019Rostered off
Employee21/02/2019Work
Employee22/02/2019Overtime
Employee23/02/2019Work
Employee24/02/2019Sick
Employee25/02/2019Work

 

Output would be as follows:

 

EmployeeSick occur after 1 day
Employee1                 1.00
Employee2                       -  

 

EmployeeSick 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
Community Support Team

Re: calculate instances of Sick leave for employee after overtime

Hi @vanschex 

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

1.png

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
Community Support Team

Re: calculate instances of Sick leave for employee after overtime

Hi @vanschex 

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

1.png

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.
vanschex Frequent Visitor
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
Community Support Team

Re: calculate instances of Sick leave for employee after overtime

Hi @vanschex 

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.
vanschex Frequent Visitor
Frequent Visitor

Re: calculate instances of Sick leave for employee after overtime

Thanks again. The following test file replicates the issue:

EmployeeDateWork Type
Employee11/02/2019Work
Employee12/02/2019Work
Employee12/02/2019Overtime
Employee13/02/2019Sick
Employee14/02/2019Sick
Employee15/02/2019Work
Employee15/02/2019Rostered off
Employee21/02/2019Work
Employee22/02/2019Overtime
Employee23/02/2019Work
Employee24/02/2019Sick
Employee25/02/2019Work
Employee16/02/2019Work
Employee17/02/2019Overtime
Employee18/02/2019Sick
Employee19/02/2019Sick
Employee110/02/2019Work
Employee111/02/2019Rostered off
employee329/06/2018Overtime
employee330/06/2018Overtime
employee31/07/2018Sick
employee32/07/2018Sick

 

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

Community Support Team
Community Support Team

Re: calculate instances of Sick leave for employee after overtime

Hi @vanschex 

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

1.png

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.
vanschex Frequent Visitor
Frequent Visitor

Re: calculate instances of Sick leave for employee after overtime

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