Forum Discussion
Solved
Count sick leave times
Dear Team, I am currently working on a HR report and I would like to have you help on this issue to caculate the sick leave freqency rate. I have the table of Employee sick leave with start d...
Vanivanivani , refer ot my blog on similar topic , see if that can help
Jihwan_Kim
Super User
Super User
Sickleave Flag : =
IF (
COUNTROWS (
FILTER (
Data,
MAX ( Dates[Date] ) >= Data[sick leave start date]
&& MIN ( Dates[Date] ) <= Data[sick leave end date]
)
) > 0,
0,
1
)
Sickleave Flag Cumulate : =
CALCULATE (
SUMX ( VALUES ( Dates[Date] ), [Sickleave Flag :] ),
FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)
How many times nonstop sickleaves? : =
VAR newtable =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
VALUES ( Dates[Date] ),
"@cumulateresult", [Sickleave Flag Cumulate :],
"@previousdatecumulateresult", CALCULATE ( [Sickleave Flag Cumulate :], DATEADD ( Dates[Date], -1, DAY ) )
),
[@cumulateresult] = [@previousdatecumulateresult]
),
[@cumulateresult]
)
RETURN
IF ( ISFILTERED ( Employees[Employee ID] ), COUNTROWS ( newtable ) )