Consecutive streak into a date range

[TerriAki]

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

[tamerj1]

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 further

Start - 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
)

[TerriAki]

@tamerj1 Amazing!  This is very close and is reliably producing the result, is there any way to adapt the code to give the open ended absence a date too?   Also the optimisation would be really appreciated as will be running this on very large dataset.  

[tamerj1]

@TerriAki 

The problem is that most probably addapting open end absence would affect the preformance but I wIll gove a try. Meanwhile, plea

 try on your full set of data and let me know how the performance is. 

[TerriAki]

I think I can handle the open ended absence by getting Power Query to put in a final row for each Employee with a break code.   Regarding optimisation, it currently isnt running on the full dataset due to running out of memory (I have 16 GB ram and have manually tried increasing Power BI cache settings).   

[tamerj1 · Accepted solution]

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 ones

Start - 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
        )
)

[TerriAki]

@tamerj1  Speed is much better now and works with my large dataset however the result is not right.  The original code was accurate.   Sample attached, you can see original code in column 'T&A Table'[Start - End (v1)] and new code in 'T&A Table'[Start - End (v2)].

https://drive.google.com/file/d/1niL5GdrIXRIVKw6DYfrYDVu5qpgkuTsU/view?usp=sharing 

[tamerj1]

Hi @TerriAki 
Everything is find, you just missed a relationship 🙂

[TerriAki]

@tamerj1 Sorry I missed that, your solution has worked out great.  I thought I might have found the limit of what DAX was capable of but your code is a work of art!  

[tamerj1]

Alright

I have some ideas. Will send you something tomorrow morning

[TerriAki]

@amitchandak Thank you.  You are right, it is a continuous streak problem.  I will retitle the post so its more clearer.  

[amitchandak]

@TerriAki , This seems like a Continuous Streak problem. But, looking at code I am not able related you ranges

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

or

Continuous streak: https://youtu.be/GdMcwvdwr0o