Forum Discussion
Date comparison help
How can I compared these 2 dates.
org_chart_employees[term_date] is part of the table and it's a date column.
My DAX is:
From your description, I have entered some sample data like the picture below:
Calendar = calendar(date(2020,1,1),today())
VARTermed = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
VARTermedFlag = IF(org_chart_employees[term_date] >= [VARTermed], "yes", "no")
VARTermedFlag = IF(org_chart_employees[term_date] >= [VARTermed], "yes", "no") always return Yes.
Because Measure is a dynamic aggregated value, in the VARTermed = CALCULATE(MIN('Calendar'[DATE]),ALLSELECTED('Calendar')) scenario, the minimum value of ‘calendar’ will always be returned.
Even if they look similar, there is a big difference between calculated columns and measures.
The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report.
A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.
I would like to suggest you take a look at below blog which told about difference between calculated column and measure:
Calculated Columns and Measures in DAX
For a better way to do the Date comparison, you can try this:
Measure = IF(MAX(org_chart_employees[Term_date]) >= [VARTermed], "yes", "no")
Here is the demo , please try it:
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi,
If you have dragged the term_date column to your visual and there is a relationship from this column to the Date column of the Calendar and the slicer (in which you select the Date) is from the Calendar table, then this measure should work
=min(org_chart_employees[term_date])>=min(Calendar[date])
From your description, I have entered some sample data like the picture below:
Calendar = calendar(date(2020,1,1),today())
VARTermed = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
VARTermedFlag = IF(org_chart_employees[term_date] >= [VARTermed], "yes", "no")
VARTermedFlag = IF(org_chart_employees[term_date] >= [VARTermed], "yes", "no") always return Yes.
Because Measure is a dynamic aggregated value, in the VARTermed = CALCULATE(MIN('Calendar'[DATE]),ALLSELECTED('Calendar')) scenario, the minimum value of ‘calendar’ will always be returned.
Even if they look similar, there is a big difference between calculated columns and measures.
The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report.
A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.
I would like to suggest you take a look at below blog which told about difference between calculated column and measure:
Calculated Columns and Measures in DAX
For a better way to do the Date comparison, you can try this:
Measure = IF(MAX(org_chart_employees[Term_date]) >= [VARTermed], "yes", "no")
Here is the demo , please try it:
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
What are you trying to compare? Do you have a date slicer on Calendar[Date] ? If you move the date slicer min date, does it change anything? Where are you putting the VARTermedFlag measure? What other columns are in the same visual context?
Hi AllisonKennedy - I am trying to compare the dates in the date filter to the termination date of an employee record so we don't display an employee if he/she was terminated during that time. The measure shows the correct MIN value and the "format" is date. BUT it's not a date column. I noticed this formula works fine in excel because both columns are dates. However, here in BI that measure is not. The measure changes if I change the filter.
Is there a way to create a custom collumn from dates in a filter? So far, I don't think it's possible otherwise, it looks at the first available data in that dataset which in this case is 2015.
marjoriefialek , Try measure like
VARTermedFlag = IF(max(org_chart_employees[term_date]) >= [VARTermed], "yes", "no")
or
VARTermedFlag = if(isblank(calculate(countrows(Table) , filter(org_chart_employees, org_chart_employees[term_date]) >= [VARTermed])) "no","yes")Hi amitchandak this didn't work. Is it possible to create a custom table from values coming from a filter? the formula works fine in excel when both are date columns. If I create a column, the first date is 1/1/2015 which is the first date of the dataset. I am trying to compare the min date of the filter to a date column in the dataset.
Thanks for your help! This worked!
