cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate sick report frequency

Hi all,

I need to show how many times an employee called in sick during a time period. I have a table of time registration data to do this. Below you will find a simplified example of this table.

Above table shows that employee 1201 called in sick on June 11 2018 for the first time (HourCode 160 = sick). On June 20 2018 he/she reported sick again. My aim is to come up with a calculation that shows that 1201 called in sick on two separate occasions.

My idea was to create a calculated column to single out the dates of the sick calls, June 11 and June 20. See below table.

I don't know if this is the best way to solve this problem and I also don't know how to create the DAX formula that would do above trick. Can you help me along?

Regards,

Erwin

6 REPLIES 6
Established Member

## Re: Calculate sick report frequency

@Erwincan you please provide a sample data to work with?

To be clear, you mentioned "that employee 1201 called in sick on June 11 2018 for the first time (HourCode 160 = sick). On June 20 2018 he/she reported sick again. " But 12,13,21 and 22 June 1201 has corresponding 160 too. What does that signify? Does it not mean he called in sick on those days too ?

If I understand it correctly, do you want to see for how many blocks of consecutive days someone called in sick ?

Regular Visitor

## Re: Calculate sick report frequency

Hi,

I believe I've written a calc that does what you're looking for:

Sick Occurence =
IF (
LOOKUPVALUE(
Sheet1[Hourcodekey],
Sheet1[Date],
CALCULATE (
MAX ( Sheet1[Date] ),
FILTER(
Sheet1,
Sheet1[Date] < EARLIER(Sheet1[Date]) &&
Sheet1[EmployeeKey] = EARLIER(Sheet1[EmployeeKey])
)
)
) <> 160
&& Sheet1[Hourcodekey] = 160
, 1
)

I tried to set this up to work across customers as well. Here's the result in the sample data:
Let me know if this works for you.

Ben
Regular Visitor

## Re: Calculate sick report frequency

Hi smpa01,

You're right, I'm trying to count how many blocks of consecutive days 1201 called in sick (2 blocks). So I'm not trying to count then number of sick days themselves (6 days).

Rg. Erwin

Regular Visitor

## Re: Calculate sick report frequency

Hi bhpage,

If I understand correctly the formula compares the hourcodekey of the current rowcontext with the hourcodekey of the preceeding rowcontext. If the hourcodekey in the current rowcontext is 160  and the hourcodekey in the preceeding rowcontext is not 160 than that is the day the employee called in sick for the first time.

Unfortunately the formula doesn't work with my real-life version of the data. The only thing different is that there are multiple employees in the real table who all have time registration data on the same dates. The error message reads "A table of multiple values was supplied where a single value was expected".

Can you solve this problem as well?

Rg. Erwin

Highlighted
Regular Visitor

## Re: Calculate sick report frequency

Can you provide an example of a customer like this?

Established Member

## Re: Calculate sick report frequency

Does this work for you?

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNCkAhCEbRvTgO0q+f11tLtP9thEQQKkSDM9CLc1LPkmVQIuGiP1hoJfV6XN+BZqFb+CwMC3K3dX4QEZYI6xt70YWJ3/tbAQfzEeUhykOLStxF4E4C14a3bW0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DT = _t, HourCode = _t, EMP = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DT", type date}, {"HourCode", Int64.Type}, {"EMP", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"HourCode", "EMP"}),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"}),
#"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom3", "Custom.2"),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Index.5", "Custom.2"}),
Custom1 = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNCkAhCEbRvTgO0q+f11tLtP9thEQQKkSDM9CLc1LPkmVQIuGiP1hoJfV6XN+BZqFb+CwMC3K3dX4QEZYI6xt70YWJ3/tbAQfzEeUhykOLStxF4E4C14a3bW0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DT = _t, HourCode = _t, EMP = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DT", type date}, {"HourCode", Int64.Type}, {"EMP", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"HourCode", "EMP"}),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")),
#"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Filtered Rows", {"FRQ"}, {"FRQ"})
in
#"Expanded Filtered Rows",
#"Merged Queries2" = Table.NestedJoin(#"Removed Other Columns",{"Custom.2"},Custom1,{"Row#"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries2", "Table1", {"DT", "HourCode", "EMP", "Row#", "Custom", "Index", "Index.1", "Custom.1", "Custom.2", "FRQ"}, {"DT", "HourCode", "EMP", "Row#", "Custom", "Index", "Index.1", "Custom.1", "Custom.2.1", "FRQ"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Table1", each ([HourCode] = 160)),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",null,1,Replacer.ReplaceValue,{"FRQ"}),
#"Merged Queries3" = Table.NestedJoin(Custom1,{"Index"},#"Replaced Value",{"Index"},"Custom1",JoinKind.LeftOuter),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries3", "Custom1", {"FRQ"}, {"FRQ.1"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Custom1", "Custom.3", each if [FRQ]=null and [FRQ.1]=null then null else if [FRQ]<>null and [FRQ.1]=null then [FRQ] else [FRQ.1]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom4",{{"FRQ", "FRQ_Start"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Custom", "Index", "Index.1", "Custom.1", "Custom.2", "FRQ.1"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Row#", Order.Ascending}}),
#"Renamed Columns1" = Table.RenameColumns(#"Sorted Rows",{{"Custom.3", "FRQ_Consecution"}})
in
#"Renamed Columns1"```