Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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:

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"
1 ACCEPTED SOLUTION
v-xiaoyan-msft
Community Support
Community Support

Hi,@marjoriefialek 

From your description, I have entered some sample data like the picture below:

 

Calendar = calendar(date(2020,1,1),today())

 

v-caitlyn-mstf_0-1608277514943.png

 

VARTermed = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))

Measure.png

 

 

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

 

v-caitlyn-mstf_2-1608277514952.png

 

 

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

Date .gif

 

 

 

 

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.

View solution in original post

7 REPLIES 7

Thanks for your help!  This worked!

 

v-xiaoyan-msft
Community Support
Community Support

Hi,@marjoriefialek 

From your description, I have entered some sample data like the picture below:

 

Calendar = calendar(date(2020,1,1),today())

 

v-caitlyn-mstf_0-1608277514943.png

 

VARTermed = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))

Measure.png

 

 

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

 

v-caitlyn-mstf_2-1608277514952.png

 

 

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

Date .gif

 

 

 

 

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
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

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

AllisonKennedy
Super User
Super User

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?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.