topic Re: Consecutive streak into a date range in DAX Commands and Tips

Consecutive streak into a date range

TerriAki — Fri, 18 Mar 2022 09:31:09 GMT

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

Re: Consecutive streak into a date range

amitchandak — Thu, 17 Mar 2022 03:21:17 GMT

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/1290346

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

Re: Consecutive streak into a date range

TerriAki — Thu, 17 Mar 2022 09:00:36 GMT

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

Re: Consecutive streak into a date range

tamerj1 — Fri, 18 Mar 2022 12:11:15 GMT

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 )

Re: Consecutive streak into a date range

TerriAki — Fri, 18 Mar 2022 13:45:19 GMT

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.

Re: Consecutive streak into a date range

tamerj1 — Fri, 18 Mar 2022 13:58:00 GMT

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.

Re: Consecutive streak into a date range

TerriAki — Fri, 18 Mar 2022 18:56:04 GMT

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

Re: Consecutive streak into a date range

tamerj1 — Fri, 18 Mar 2022 18:58:32 GMT

Alright

I have some ideas. Will send you something tomorrow morning

Re: Consecutive streak into a date range

tamerj1 — Sat, 19 Mar 2022 07:12:02 GMT

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

Re: Consecutive streak into a date range

TerriAki — Sun, 20 Mar 2022 21:24:23 GMT

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

Re: Consecutive streak into a date range

tamerj1 — Mon, 21 Mar 2022 09:42:55 GMT

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

Re: Consecutive streak into a date range

TerriAki — Mon, 21 Mar 2022 11:59:49 GMT

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!