Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Chris2016
Resolver I
Resolver I

Create custom column checking if date is in interval of dates

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:

NameDeliveryStartEnd
JohnApple5/1/201812/15/2018
JohnApple12/16/20184/1/2019
JohnApple4/2/201912/1/2019
JohnApple12/2/20193/15/2020
JohnPear3/16/20208/10/2020
JohnPear8/11/202011/15/2020
JohnStrawberry11/16/20206/1/2021
JohnStrawberry6/2/20215/1/2022
JohnStrawberry5/2/20225/1/2023
JennyApple6/12/201811/15/2018
JennyApple11/16/20185/1/2019
JennyApple5/2/201910/1/2019
JennyApple10/2/20196/15/2020
JennyPear6/16/202011/10/2020
JennyPear11/11/202011/15/2020
JennyStrawberry11/16/20208/1/2021
JennyStrawberry8/2/202112/1/2022
JennyChery12/2/20223/1/2023
LilyPlum9/1/201812/30/2018
LilyPlum12/31/20185/1/2019
LilyApple5/2/201911/1/2019
LilyApple11/2/20195/15/2020
LilyPear5/16/20207/10/2020
LilyPear7/11/202012/15/2020
LilyStrawberry12/16/20204/1/2021
LilyChery4/2/20218/1/2022
LilyChery8/2/202212/1/2023

 

User table:

NameLeave Date
John4/10/2020
Jenny 
Lily3/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:

 

NameLeave DateCustom Column
John4/10/2020Pear
Jenny Strawberry
Lily3/4/2021Strawberry

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

 

Jihwan_Kim_1-1667817083157.png

 

 

Jihwan_Kim_0-1667817060045.png

 

 

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
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

 

Jihwan_Kim_1-1667817083157.png

 

 

Jihwan_Kim_0-1667817060045.png

 

 

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
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Hi, Jihvan,

This is exactly what I need. Big thank you!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.