Create custom column checking if date is in interval of dates

Chris2016 — Mon, 07 Nov 2022 09:50:26 GMT

Hello,

I have two tables: one containing user names, with a delivery type and an interval when the delivery was active (end date and start date). The second table contains the user name and a date when that user was on leave (not all users have a leave date).
Delivery table:

Name	Delivery	Start	End
John	Apple	5/1/2018	12/15/2018
John	Apple	12/16/2018	4/1/2019
John	Apple	4/2/2019	12/1/2019
John	Apple	12/2/2019	3/15/2020
John	Pear	3/16/2020	8/10/2020
John	Pear	8/11/2020	11/15/2020
John	Strawberry	11/16/2020	6/1/2021
John	Strawberry	6/2/2021	5/1/2022
John	Strawberry	5/2/2022	5/1/2023
Jenny	Apple	6/12/2018	11/15/2018
Jenny	Apple	11/16/2018	5/1/2019
Jenny	Apple	5/2/2019	10/1/2019
Jenny	Apple	10/2/2019	6/15/2020
Jenny	Pear	6/16/2020	11/10/2020
Jenny	Pear	11/11/2020	11/15/2020
Jenny	Strawberry	11/16/2020	8/1/2021
Jenny	Strawberry	8/2/2021	12/1/2022
Jenny	Chery	12/2/2022	3/1/2023
Lily	Plum	9/1/2018	12/30/2018
Lily	Plum	12/31/2018	5/1/2019
Lily	Apple	5/2/2019	11/1/2019
Lily	Apple	11/2/2019	5/15/2020
Lily	Pear	5/16/2020	7/10/2020
Lily	Pear	7/11/2020	12/15/2020
Lily	Strawberry	12/16/2020	4/1/2021
Lily	Chery	4/2/2021	8/1/2022
Lily	Chery	8/2/2022	12/1/2023

User table:

Name	Leave Date
John	4/10/2020
Jenny
Lily	3/4/2021

I need help creating a custom column that looks to see if the Leave Date in the User table is in the Start and End date interval of the Delivery table and take the relevant Delivery type of that period. If there is no date in the User table, then the static reference date by which the search should happen is 3/15/2021.

The result should be something like this:

Name	Leave Date	Custom Column
John	4/10/2020	Pear
Jenny		Strawberry
Lily	3/4/2021	Strawberry

^ For Jenny, the custom column should say Strawberry, because 3/15/2021 is in the 11/16/2020 - 8/1/2021 interval when the delivery was Strawberry.

Thanks a lot!

Re: Create custom column checking if date is in interval of dates

Jihwan_Kim — Mon, 07 Nov 2022 10:31:42 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Expected result CC = VAR _result = MAXX ( FILTER ( RELATEDTABLE ( Delivery ), Delivery[Start] <= User[Leave Date] && Delivery[End] >= User[Leave Date] ), Delivery[Delivery] ) RETURN IF ( _result = BLANK (), MAXX ( FILTER ( RELATEDTABLE ( Delivery ), Delivery[Start] <= DATE ( 2021, 3, 15 ) && Delivery[End] >= DATE ( 2021, 3, 15 ) ), Delivery[Delivery] ), _result )

Re: Create custom column checking if date is in interval of dates

Chris2016 — Mon, 07 Nov 2022 12:52:55 GMT

Hi, Jihvan,

This is exactly what I need. Big thank you!

topic Create custom column checking if date is in interval of dates in DAX Commands and Tips

Create custom column checking if date is in interval of dates

Re: Create custom column checking if date is in interval of dates

Re: Create custom column checking if date is in interval of dates