Help on holdays calculation

JamesFR06 — Thu, 13 Jun 2024 16:51:27 GMT

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

Re: Help on holdays calculation

Greg_Deckler — Thu, 13 Jun 2024 19:31:22 GMT

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

topic Re: Help on holdays calculation in DAX Commands and Tips

Help on holdays calculation

Re: Help on holdays calculation