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

     

john_hqi's avatar
john_hqi
New Member
9 years ago

This looks like it will work thanks everyone!

dmacgill's avatar
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])))

 

                  

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Share the link from where i can download your file.