First and Last Dates in Sequence

[StevenF19]

Hello,

I've got a dataset of employee timesheet data, with each row representing a single day of time submitted by a particular employee, with the column [ShiftDate] being the day that time is applicable for. I am looking to track consecutive days of time for employees and then grab the earliest and latest [ShiftDate] from that sequence. I've been able to create a [Sequence] column that shows the number of consecutive days at that point but haven't been able to progress further. How would I go about doing this?

[smpa01 · Accepted solution]

@StevenF19  you would need a additional column called Grouping which assigns the same value to consecutive dates and the Grouping increases when consecution breaks like following

which you can generate by following PQ - both Grouping and Sequence

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEyNNQ3MtM3MjAyVIrVQRU2xy5sgVXY2AAhDBI1wzQbWRhmdiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeNum = _t, ShiftDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeNum", Int64.Type}, {"ShiftDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EmployeeNum"}, {{"ad", each _, type table [EmployeeNum=nullable number, ShiftDate=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x =[ad],
    ShiftDate=x[ShiftDate],
    Custom1 = List.Generate(
        ()=>[i=0,k=ShiftDate{i},j=1],
        each [i]<List.Count(ShiftDate),
        each [i=[i]+1,k=ShiftDate{i},j=if Duration.Days(k-[k])=1 then [j] else [j]+1], each [j]),
    Custom2 = Table.ColumnNames([ad]),
    Custom3 = Table.ToColumns([ad]),
    Custom4 = List.Combine({Custom2,{"Grouping"}}),
    Custom5 = Table.FromColumns(Custom3&{Custom1},Custom4)   
in
    Custom5),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"EmployeeNum", "ShiftDate", "Grouping"}, {"EmployeeNum", "ShiftDate", "Grouping"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"EmployeeNum", "Grouping"}, {{"ad", each _, type table [EmployeeNum=number, ShiftDate=date, Grouping=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom.1", each let x = [ad],
    y = Table.Sort(x,{{"ShiftDate", Order.Ascending}}),
    z = Table.AddIndexColumn(y, "Sequence", 1, 1, Int64.Type) in z),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.1", {"EmployeeNum", "ShiftDate", "Grouping", "Sequence"}, {"EmployeeNum", "ShiftDate", "Grouping", "Sequence"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"EmployeeNum", Int64.Type}, {"ShiftDate", type date}, {"Grouping", Int64.Type}, {"Sequence", Int64.Type}})
in
    #"Changed Type1"

then you can write the following DAX measure to give you want you need

_maxDatebyEmpGrouping = CALCULATE(MAX('Table 1'[ShiftDate]),ALLEXCEPT('Table 1','Table 1'[EmployeeNum],'Table 1'[Grouping]))

_minDatebyEmpGrouping = CALCULATE(MIN('Table 1'[ShiftDate]),ALLEXCEPT('Table 1','Table 1'[EmployeeNum],'Table 1'[Grouping]))

pbix is attached

[smpa01]

@StevenF19  did you have a chance to try out the above?

[smpa01]

@StevenF19  can you start by providing sample data please?

[StevenF19]

Sure, here's some sample data showing the data structure. Sequence is generated through a calculated column and if it's not needed for this calculation it can be removed.

EmployeeNum	ShiftDate	Sequence
12345	11/26/2021	1
12345	11/27/2021	2
12345	11/28/2021	3
12345	11/30/2021	1
23456	11/26/2021	1
23456	11/27/2021	2