cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
john_hqi Frequent Visitor
Frequent Visitor

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

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Determine if date is between 2 dates

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
Super User
Super User

Re: Determine if date is between 2 dates

I wonder if you could just use MONTH and then multiply by the YEAR - a baseline. So, figure out the quarter, 1, 2, 3, 4 and then multiply by YEAR-2017+1 for example. Might cut down on the DAX IF statement nesting, four instead of 12.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

DominikPetri
Advisor

Re: Determine if date is between 2 dates

Instead of using nested IFs, have a look at the SWITCH function:

https://msdn.microsoft.com/en-us/library/gg492166.aspx

 

Hope this helps

Dominik.

 

Please mark Accept as Solution if your question is answered. Kudos gladly accepted. ⇘
mmace1 Member
Member

Re: Determine if date is between 2 dates

If you may need to use this "calculation" for the quarter in more than just this one table, it may be worthwhile to setup a calender table.  So, a table with all dates in one column, and anther column (with whatever formula you settle on - SWITCH, etc.), that gives the quarter.


Then the next time you need the quarter added to a table, instead of having to rewrite the formula, you could just link to the calender table and pull over the quarter from there.

 

This can also be handy if you end up with data from several tables on one page, and want to have a single quarter filter that applies to all the data on the page. 

v-jiascu-msft Super Contributor
Super Contributor

Re: Determine if date is between 2 dates

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

john_hqi Frequent Visitor
Frequent Visitor

Re: Determine if date is between 2 dates

This looks like it will work thanks everyone!

dmacgill Frequent Visitor
Frequent Visitor

Re: Determine if date is between 2 dates

Not being able to get this to work. Adding a New Column to calcaute the Period a date falls between and getting error - table of multiple vaules was supplied when a single value was expected.

 

Date Period = CALCULATE (VALUES (Period[PERIOD]),FILTER (Period,Period[START_DATE]<=EARLIER(SCOMMON[Dates])&&Period[END_DATE]>=EARLIER(SCOMMON[Dates])))

 

Period.PNG                   dates.PNG

Super User
Super User

Re: Determine if date is between 2 dates

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mazharmh Member
Member

Re: Determine if date is between 2 dates

Hi @v-jiascu-msft,

 

I have bit simillar requirment. I have to calucalte quaterly sales perfromance of the companey. But My boss want quarter should start from first monday of the quarter. rang is like that. 

image.png

 

 

 

 

 

 

v-jiascu-msft Super Contributor
Super Contributor

Re: Determine if date is between 2 dates

Hi @mazharmh,

 

Please open a new thread. One thread one topic and this thread has been closed. Thanks for your understanding.

 

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 180 members 2,338 guests
Please welcome our newest community members: