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

     

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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

 

Anonymous's avatar
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's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft 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