Date comparison help

[marjoriefialek]

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:

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"

[marjoriefialek]

Thanks for your help!  This worked!

[v-xiaoyan-msft · Accepted solution]

Hi,@marjoriefialek 

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/linli_qiuyunus_onmicrosoft_com/EYjKO865mD5GpX3aZv3...

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.

[Ashish_Mathur]

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])

[amitchandak]

@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")

[marjoriefialek]

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. 

[AllisonKennedy]

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?

[marjoriefialek]

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.