How To Get Multiple Min and Max Occurences in Dax

[casabs]

Hi

I am trying to return periods of holiday for some colleagues in my organisation but can't find a way in Dax to achive this. Equally if it can be acheived in SQL, that is how the data is currently stored

Desired Result:

Colleague 123: Blank/No Holiday

Colleague 456: 02/02/2024 - 03/02/2024

Colleague 789: 02/02/2024 - 03/02/2024

                         06/02/2024 - 08/02/2024

The table is currently vertically strucutured with a combined Key of colleague ID and Date

Example below:

Date	Colleague ID	Holiday
01/02/2024	123	0
02/02/2024	123	0
03/02/2024	123	0
04/02/2024	123	0
05/02/2024	123	0
06/02/2024	123	0
07/02/2024	123	0
08/02/2024	123	0
01/02/2024	456	0
02/02/2024	456	7
03/02/2024	456	7
04/02/2024	456	0
05/02/2024	456	0
06/02/2024	456	0
07/02/2024	456	0
08/02/2024	456	0
01/02/2024	789	0
02/02/2024	789	7
03/02/2024	789	7
04/02/2024	789	0
05/02/2024	789	0
06/02/2024	789	7
07/02/2024	789	7
08/02/2024	789	7

[rbriga]

Alright, I mistook the direction you need to go. 

Let's try more SQL, based on this Stack Overflow post. 

CASE HOLIDAY
WHEN 0 THEN NULL
ELSE
MAX(CASE WHEN HOLIDAY = 0 THEN DATEADD(DAY,1,DATE) END) OVER (PARTITION BY COLLEAGUE_ID ORDER BY DATE by date) 
END as VACATION START,
CASE HOLIDAY
WHEN 0 THEN NULL
ELSE
MIN(CASE WHEN HOLIDAY = 0 THEN DATEADD(DAY,-1,DATE) END) OVER (PARTITION BY COLLEAGUE_ID ORDER BY DATE by date DESC) 
END as VACATION END,

Does that create the desired start and end dates?

If so, you can then use that as a temporary table (WITH T AS) and SELECT DISTINCT

[rbriga]

Let's solve this in SQL- like you've hinted, it's best to push the calculations to the source.

I'm assuming you have a date table [DATE_TABLE] with the date field [DATE_VALUE].

Also, you have the table [VACATION_TABLE] with VACATION_START and VACATION_END.

Now:

SELECT 
	VACATION.Colleague_ID,
	DATES.DATE_VALUE,
	'1'	AS "Vacation Day"
FROM VACATION_TABLE VACATION
INNER JOIN DATE_TABLE DATES
ON DATES.DATE_VALUE >= DATE(VACATION.VACATION_START) AND DATES.DATE_VALUE <=DATE(VACATION.VACATION_END)

(If VACATION_START and VACATION_END are already dates, you don't need DATE() )

So if colleague 123 was out between  2024-01-04 and 2024-01-11, this will return:

ID  DATE_VALUE Vacation Day

123	2024-01-04	1
123	2024-01-05	1
123	2024-01-06	1
123	2024-01-07	1
123	2024-01-08	1
123	2024-01-09	1
123	2024-01-10	1
123	2024-01-11	1

Does this work?

[casabs]

Hey thanks for the reply

I don't have the vacation start and end date in that format, and that is part of the challenge. The table has a date column, and a vacation column.

In the vacation column the value is 0 for days with no vacation and then a positive integer (representing number of hours) for days when holiday is booked. 

the problem with min(date) and max(date) where holiday > 0 will only give me one date for min and max, not each occurence