Help on holdays calculation

[JamesFR06]

Hi,

This is my table :

Matricule	Beg_Date	Ending_Date	Reason	Type Abs	Dab_Abs	End_Abs	Duration	Dur_rk
S21095	08/02/2022	08/02/2022	FORMATION	Consentie	08/02/2022	10/02/2022	3	Less 5 days
S21095	09/02/2022	09/02/2022	FORMATION	Consentie	08/02/2022	10/02/2022	3	Less 5 days
S21095	10/02/2022	10/02/2022	FORMATION	Consentie	08/02/2022	10/02/2022	3	Less 5 days
S21095	05/04/2022	05/04/2022	ABSENCE NON PAYEE	Subie	05/04/2022	05/04/2022	1	Less 5 days
S21095	27/05/2022	27/05/2022	CONGES	Consentie	27/05/2022	27/05/2022	1	Less 5 days
S21095	06/06/2022	06/06/2022	CONGES	Consentie	06/06/2022	06/06/2022	1	Less 5 days

My problem is due to the HR system sometimes for a Holiday period I can have many rows. I cannot do it on query !!

I would like to add these 4 columns in my table. SO  when there is only 1 line it is ok, more I do not succeed to achieve this.

The code should read each row and look at the difference between the previous End Abs row value and the beg value of the active row. And if difference is 1 it is the same period etc.....

ANy help would be very appreciate !! 

[Greg_Deckler · Accepted solution]

@JamesFR06 So, perhaps something like this, although this would not be 100% foolproof:

Dab_Abs Column = 
  VAR __Matricule = [Matricule]
  VAR __Reason = [Reason]
  VAR __TypeAbs = [Type Abs]
  VAR __Table = FILTER( 'Table', [Matricule] = __Matricule && [Reason] = __Reason && [Type Abs] = __TypeAbs)
  VAR __Resut = MINX( __Table, [Beg_Date] )
RETURN
  __Result


End_Abs Column = 
  VAR __Matricule = [Matricule]
  VAR __Reason = [Reason]
  VAR __TypeAbs = [Type Abs]
  VAR __Table = FILTER( 'Table', [Matricule] = __Matricule && [Reason] = __Reason && [Type Abs] = __TypeAbs)
  VAR __Resut = MAXX( __Table, [Ending_Date] )
RETURN
  __Result


Duration = ([End_Abs Column] - [Dab_Abs Column]) * 1.

Dur_rk = IF( [Duration] < 5, "Less 5 days", "5 day or more" )