Compare Dates

[marjoriefialek]

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"

[v-janeyg-msft · Accepted solution]

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.

[marjoriefialek]

Thanks for your answer.  This will help me. 

[d_gosbell]

---

@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.