Forum Discussion

john_hqi's avatar
john_hqi
New Member
9 years ago
Solved

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 ...
Need Help
  • v-jiascu-msft's avatar
    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

     

mmace1's avatar
mmace1
Icon for Impactful Individual rankImpactful 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.