Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
john_hqi
New Member

Determine if date is between 2 dates

‎07-10-2017 08:02 AM

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

 

Labels:
Message 1 of 15
143,568 Views
0
1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

‎07-11-2017 12:29 AM

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

Message 5 of 15
158,702 Views
14 REPLIES 14

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All