Forum Discussion

marjoriefialek's avatar
marjoriefialek
Helper I
5 years ago
Solved

Compare Dates

Need help with comparing dates.  DATEDIFF doesn't work.  How can I compare those 2 dates?  

 

org_chart_employees[term_date] is part of the table and it's a date column. 

My DAX is:

I set up meausure:
VARTermed = CALCULATE(MIN('Calendar'[DATE]),ALLSELECTED('Calendar'))
I set up column:
VARTermedFlag = IF(org_chart_employees[term_date] >= [VARTermed], "yes", "no")
 
they are all "Yes"
 
 

 

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, marjoriefialek 

     

    It’s my pleasure to answer for you.

    According to your description,I think you may have some confusion about measure and column.

    The calculation of 'column' precedes 'measure'. Your column references a measure. At this time, the measure has no specific context, which is equivalent to calculating in all (table). The measure calculated is always the minimum value in the entire table. So >= is always true and always "yes".

    The column can't be changed interactively with the slicer. You need to convert the column to measure.

    Like this:

    VARTermedFlag =
IF ( MAX ( org_chart_employees[term_date] ) >= [VARTermed], "yes", "no" )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    5 years ago
    marjoriefialek wrote:
    they are all "Yes"
     

    This is because columns are caculated and stored when the refresh operation is run. And at that time no selections are active so the ALLSELECTED() call in your measure returns all the rows. You cannot create a calculated column which changes dynamically in response to slicers or cross filters.

     

    I'm not sure what you are trying to do here but you need to look at some way of incorporating your date check into a measure rather than using a column.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, marjoriefialek 

     

    It’s my pleasure to answer for you.

    According to your description,I think you may have some confusion about measure and column.

    The calculation of 'column' precedes 'measure'. Your column references a measure. At this time, the measure has no specific context, which is equivalent to calculating in all (table). The measure calculated is always the minimum value in the entire table. So >= is always true and always "yes".

    The column can't be changed interactively with the slicer. You need to convert the column to measure.

    Like this:

    VARTermedFlag =
IF ( MAX ( org_chart_employees[term_date] ) >= [VARTermed], "yes", "no" )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.