calculate instances of Sick leave for employee after overtime

[vanschex]

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

[v-cherch-msft]

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

Regards,

[vanschex]

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

[v-cherch-msft]

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,

[vanschex]

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

[v-cherch-msft · Accepted solution]

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

Regards,

[vanschex]

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