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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
v-jiascu-msft
Employee
Employee

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.
Anonymous
Not applicable

Hello @v-jiascu-msft ,

 

Your solution may help my case too. Can you please tell me what is the relationship between the two tables (quarters and calendar tables) you are using? (i.e. what datafields in the two tables you are using to connect the two tables?)

 

Everything works fine for me in the formula until the function EARLIER. Just after the EARLIER function when I am trying to type the date field from my calendar table I don't have the list of the available data fields to choose it. I believe I did not connected the two tables properly. I tried to connect the field "Start date" in Quarters table with field "Date" in date teble (not sure if this is correct). 

 

Many thanks

Hi @Anonymous,

 

Actually, no relationship is there. No EARLIER is needed either. Please refer to the file in the attachment. I have improved it.

 

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.

Hi @Silko,

 

I'm glad it can help you. Please open a new thread of your question.

 

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.

Here is the new thread. I was not sure what the subject should be so it was simpler to ask the question here. 🙂

Anonymous
Not applicable

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

 

 

 

 

 

 

Hi @Anonymous,

 

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.

This looks like it will work thanks everyone!

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

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mmace1
Impactful Individual
Impactful Individual

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. 

DominikPetri
Advocate V
Advocate V

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. ⇘
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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