cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
john_hqi
New Member

Determine if date is between 2 dates

I have a table with an End Date for a record. I need to determine if this end date is between a start date and end date for a custom 5 year contract quarter. I was thinking of just doing this in DAX as a calculated column and nesting an if statement to retrun the correct value. Is there a more "correct" way to do this? I have 12 quarters 9-20 that I have to look for.

 

For example:

IF([EndDate] >= DATE(2017,5,1) && [EndDate] <= DATE(2017,7,31), "Q12", 

     IF([EndDate] >= DATE(2017,8,1) && [EndDate] <= DATE(2017,10,31), "Q13", 

          IF([EndDate] >= DATE(2017,11,1) && [EndDate] <= DATE(2018,1,31), "Q14", 

               "Not in range"))

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

Hi @john_hqi

 

I tested @mmace1's idea. It works. Please have a try.

1. Create a table "Quarters".

2. Add a calculated column:

Column =
CALCULATE (
    VALUES ( Quarters[Value] ),
    FILTER (
        'Quarters',
        'Quarters'[Start] <= EARLIER ( 'Calendar'[Date] )
            && 'Quarters'[End] >= EARLIER ( 'Calendar'[Date] )
    )
)

3. If more quarters neeed, we can add to table "Quarters".Determine if date is between 2 dates.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors