Absence report - counting n° of days for each sick period to determine Short, mid & Long term sickness days

[LokiSonic]

Hi everyone,

I'm having some problems to determine categories for the sick days at our company.

• I have a dataset of all employees with a row per day for each employee. To make it easier, I can filter these on workdays only.
• I then have a column that gives me the type of absence for that day (for instance: Sick day, Leave, Vacation). In total there are 7 different type of absences but i'm only interested in Sick days.

The solution I'm trying to get:

• I want to be able to categorize the sickness periods to one of 3 category's: Short Term (1-21 consecutive workdays), Mid Term (22 to & 126 consecutive workdays) and Long term (+126 consecutive workdays).
• So I need a way to count consecutive (work)days when the Type of absence equals Sickness ("Z" in my dataset).
• Ideally I want to do this in Power Query or Power Pivot. So me or the HR-users can just refresh the file and get the updated report.

My company does not allow me to install the plugin, I will see if I can upload a spreadsheet later tonight.

I hope someone can help me with this, because I just can't get a working solution and I can't find anything on the message board that does the same kind categorization as I want to do.
Big thanks in advance for whomever can help me with this.

 

[alex78]

Hi @LokiSonic,

Assuming the source table is 'Name', 'List of Dates' and 'Type' columns, try the following M code,

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    ColName = Table.ColumnNames(Source),
    #"Day Counter" = Table.Combine(
      Table.Group(Source, {ColName{0}, ColName{2}}, 
      {"Count", each let x = Table.RowCount(_) in 
      Table.AddColumn(_, "Consecutive Day Counter", each if [Type] = "Z" then x else null)}, 0)[Count]),
    #"Duration Type" = Table.AddColumn(#"Day Counter", "Duration Type",
    each try let x = {[Consecutive Day Counter] <= 21,[Consecutive Day Counter] >= 22 and [Consecutive Day Counter] <= 126,[Consecutive Day Counter] >= 127},
    Result = {"Short Term Illness","Mid Term Illness","Long Term Illness"} in Result{List.PositionOf(x, true)} otherwise null),
    #"Changed Type" = Table.TransformColumnTypes(#"Duration Type",{{ColName{1}, type date}, {"Consecutive Day Counter", Int64.Type}})
in
    #"Changed Type"

Regards,

​