Calculate sick report frequency

[Erwin]

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

[Anonymous]

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

[Anonymous]

Thanks a lot! I get the following error when I try to apply this calculation in a similar dataset with multiple similar dates:
A table of multiple values was supplied where a single value was expected.

Dataset example:

Date	Start time	Hourcodekey	employeekey	Sick occurence should be:
1-6-2020	09:00	103	1201
2-6-2020	09:00	103	1201
3-6-2020	09:00	160	1205	1
4-6-2020	09:00	160	1205
4-6-2020	09:00	103	1201
4-6-2020	15:00	103	1201
5-6-2020	09:00	103	1201
6-6-2020	09:00	160	1201	1
6-6-2020	09:00	160	1205	1
7-6-2020	09:00	103	1201
8-6-2020	09:00	103	1201
8-6-2020	09:00	160	1205	1
8-6-2020	09:00	160	1205
9-6-2020	09:00	103	1201
10-6-2020	09:00	103	1201
11-6-2020	09:00	103	1201
12-6-2020	09:00	160	1201	1
12-6-2020	15:00	160	1201
12-6-2020	09:00	103	1205
14-6-2020	09:00	103	1201
15-6-2020	09:00	103	1201

Hope someone can help us out

[Erwin]

Hi bhpage,

Thanks for your response.

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

[Anonymous]

Can you provide an example of a customer like this?

[smpa01]

@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 ?

[Erwin]

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

[smpa01]

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"}),
    #"Added Index2" = Table.AddIndexColumn(#"Filled Down", "Row#", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index2", "Custom", each if [HourCode]=160 then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom.2", each Text.From([Custom.1])&Text.From([Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"}),
    #"Added Index3" = Table.AddIndexColumn(#"Removed Columns", "Index.2", 1, 1),
    #"Added Index4" = Table.AddIndexColumn(#"Added Index3", "Index.3", 0, 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index4",{"Index.2"},#"Added Index4",{"Index.3"},"Added Index4",JoinKind.LeftOuter),
    #"Expanded Added Index4" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index4", {"Index"}, {"Index.4"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Added Index4", "Custom.1", each if [Index.4]<> null then [Index.4]-1 else Table.RowCount(#"Filled Down")),
    #"Added Index5" = Table.AddIndexColumn(#"Added Custom2", "Index.5", 1, 1),
    #"Added Custom3" = Table.AddColumn(#"Added Index5", "Custom.2", each {[Index]+1..[Custom.1]}),
    #"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"}),
    #"Added Index2" = Table.AddIndexColumn(#"Filled Down", "Row#", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index2", "Custom", each if [HourCode]=160 then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom.2", each Text.From([Custom.1])&Text.From([Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "FRQ", each 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom1",{"Row#"},#"Added Custom2",{"Row#"},"Filtered Rows",JoinKind.LeftOuter),
    #"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"