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

Need Help

v-jiascu-msft
9 years ago
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".

Best Regards!
Dale

15 Replies

v-jiascu-msft
Microsoft Employee
9 years ago
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".

Best Regards!
Dale
- john_hqi
  New Member
  9 years ago
  This looks like it will work thanks everyone!
  - dmacgill
    Frequent Visitor
    8 years ago
    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])))
- Anonymous
  Not applicable
  8 years ago
  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.
  - v-jiascu-msft
    Microsoft Employee
    8 years ago
    Hi Anonymous,
    
    Please open a new thread. One thread one topic and this thread has been closed. Thanks for your understanding.
    
    Best Regards,
    Dale
- Silko
  Helper II
  8 years ago
  v-jiascu-msft your answer helped me a lot but I still have one problem. My example is a little bit different. Instead of quarters I want to append a column with user id who was absent on specfied day:
  date | user id
  1.1.2017 | 1
  2.1.2017 | 1
  3.1.2017 | 2
  ...
  
  I have a table of users absence:
  start_date | end_date | user_id
  1.1.2017 2.1.2017 1
  3.1.2017 3.1.2017 2
  ...
  
  Your example works perfect if there is only one user absent on one day. But muliple users can be absent on one day. So in that case I need the table to be something like (user 1 and 2 both absent on 2.1.2017):
  date | user id
  1.1.2017 | 1
  2.1.2017 | 1
  2.1.2017 | 2
  3.1.2017 |
  ...
  
  Can you or anyone else help?
  - v-jiascu-msft
    Microsoft Employee
    8 years ago
    Hi Silko,
    
    I'm glad it can help you. Please open a new thread of your question.
    
    Best Regards,
    Dale
mmace1
Impactful Individual
9 years ago
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.
Greg_Deckler
Community Champion
9 years ago
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.
DominikPetri
Advocate V
9 years ago
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.