Forum Discussion
Consecutive streak into a date range
Hi,
Kinda new here and would really appreciate some help 🙂
I have a time and attendance table and trying to work out the continues period of sick absence with a value of S, SC or SB for each employee. Start and End added below as an example of what the output should be (could be summarised into a seperate table if needed. In order to work out continous periods of absence the calculation will need to take into account two tables of codes. One contains a list of codes that would break a continuous period and another list which does not.
The streak must start and end S, SB or SC.
T&A table
| Employee ID | Date | Status | Start | End |
| 123 | 02-Feb-22 | RD | ||
| 123 | 03-Feb-22 | 1 | ||
| 123 | 04-Feb-22 | S | 04/02/2022 | 04/02/2022 |
| 123 | 05-Feb-22 | RD | ||
| 123 | 06-Feb-22 | RD | ||
| 123 | 07-Feb-22 | 1 | ||
| 123 | 08-Feb-22 | S | ||
| 123 | 09-Feb-22 | SC | ||
| 123 | 10-Feb-22 | SB | 08/02/2022 | 10/02/2022 |
123 | 11-Feb-22 | 1 | ||
| 123 | 12-Feb-22 | RD | ||
| 123 | 13-Feb-22 | RD | ||
| 123 | 14-Feb-22 | AL | ||
| 123 | 15-Feb-22 | S | ||
| 123 | 16-Feb-22 | RD | ||
| 123 | 17-Feb-22 | S | ||
| 123 | 18-Feb-22 | S | ||
| 123 | 19-Feb-22 | ME | ||
| 123 | 20-Feb-22 | RD | ||
| 123 | 21-Feb-22 | S | ||
| 123 | 22-Feb-22 | S | 15/02/2022 | 22/02/2022 |
| 123 | 23-Feb-22 | RD |
| Break codes | Continuous codes | |
| 01 | AW | |
| ET | BH | |
| L | HA | |
| NW | ME | |
| RW | OD | |
| RX | RD | |
| TC | RB | |
| TW | BL | |
| WB | HR | |
| WS | PL | |
| XX | S | |
| ZL | SB | |
| AB | SC | |
| AL | ||
| AS | ||
| CL | ||
| EL | ||
| FL | ||
| HD | ||
| HL | ||
| LC | ||
| MB | ||
| ML | ||
| TA | ||
| UL |
Hi TerriAki
I was able to double the speed by creating relationships as per below screenshot. I tried on 2.7M rows table and still takes around 50-55 sec. on my machine which is not super-fast. Still too slow and also you have to know that the time increases exponentially with the number of rows and I have no idea how many columns you have. If you have too many columns we need to select only the relevant onesStart - End = IF ( 'T&A table 2'[Status] IN 'Sick Absence Code', VAR CurrentDate = 'T&A table 2'[Date] VAR EmployeeTable = CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2', 'T&A table 2'[Employee ID] ) ) VAR OffDaysTable = CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2','T&A table 2'[Employee ID] ), 'Sick Absence Code' ) VAR BreakDaysTable = CALCULATETABLE ( 'T&A table 2', ALLEXCEPT ( 'T&A table 2','T&A table 2'[Employee ID] ), 'Break codes' ) -- Calculating last day off VAR NexBreaksTable = FILTER ( BreakDaysTable, 'T&A table 2'[Date] >= CurrentDate ) VAR NextBreakDate = MINX ( NexBreaksTable, 'T&A table 2'[Date] ) VAR NextOffDaysTable = FILTER ( OffDaysTable, 'T&A table 2'[Date] < NextBreakDate ) VAR LastDayOff = MAXX ( NextOffDaysTable, 'T&A table 2'[Date] ) RETURN IF ( CurrentDate = LastDayOff, -- Calculating first day off VAR PreviousBreaksTable = FILTER ( BreakDaysTable, 'T&A table 2'[Date] <= CurrentDate ) VAR PreviousBreakDate = MAXX ( PreviousBreaksTable, 'T&A table 2'[Date] ) VAR PreviousOffDaysTable = FILTER ( OffDaysTable, 'T&A table 2'[Date] > PreviousBreakDate ) VAR FirstDayOff = MINX ( PreviousOffDaysTable, 'T&A table 2'[Date] ) VAR Result = FirstDayOff & " - " & LastDayOff RETURN Result ) )
11 Replies
TerriAki , This seems like a Continuous Streak problem. But, looking at code I am not able related you ranges
or
Continuous streak: https://youtu.be/GdMcwvdwr0o
Hi TerriAki
Please find attached sample file with the solution https://www.dropbox.com/t/WHjQ0GfVCTQ62ua7
It is not the optimum performance code but it should work and do the job.At the end of the sample data I have added a break as otherwise the sick leave is considred open. I hope this shall not be a problem to you.
I will let you know If I was able to optimize it furtherStart - End = IF ( 'T&A table'[Status] IN { "S", "SB", "SC" }, VAR CurrentDate = 'T&A table'[Date] VAR EmployeeTable = CALCULATETABLE ( 'T&A table', ALLEXCEPT ( 'T&A table', 'T&A table'[Employee ID] ) ) VAR OffTable = FILTER ( EmployeeTable, 'T&A table'[Status] IN { "S", "SB", "SC" } ) VAR BreakTable = FILTER ( EmployeeTable,'T&A table'[Status] IN 'Break codes' ) -- Calculating last day off VAR NextDaysTable = FILTER ( EmployeeTable, 'T&A table'[Date] >= CurrentDate ) VAR NexBreaksTable = FILTER ( NextDaysTable, 'T&A table'[Status] IN 'Break codes' ) VAR NextBreakDate = MINX ( NexBreaksTable, 'T&A table'[Date] ) VAR NextOffDaysTable = FILTER ( OffTable, 'T&A table'[Date] < NextBreakDate ) VAR LastDayOff = MAXX ( NextOffDaysTable, 'T&A table'[Date] ) -- Calculating first day off VAR PreviousDaysTable = FILTER ( EmployeeTable, 'T&A table'[Date] <= CurrentDate ) VAR PreviousBreaksTable = FILTER ( PreviousDaysTable, 'T&A table'[Status] IN 'Break codes' ) VAR PreviousBreakDate = MAXX ( PreviousBreaksTable, 'T&A table'[Date] ) VAR PreviousOffDaysTable = FILTER ( OffTable, 'T&A table'[Date] > PreviousBreakDate ) VAR FirstDayOff = MINX ( PreviousOffDaysTable, 'T&A table'[Date] ) VAR Result = IF ( CurrentDate = LastDayOff, FirstDayOff & " - " & LastDayOff ) RETURN Result )amitchandak Thank you. You are right, it is a continuous streak problem. I will retitle the post so its more clearer.
