Max date form different table with filter

[Anonymous]

Hi all,

I'm having issues with comparing two dates in different tables. One is a table with employee info, the other one a datetable.I I'm trying to create a measure which return True when the date out of service is earlier then the max date of the selected period, so that I can filter out the people that where no longer in service in a selected period.

For the measure Out of Service Today the following code works fine:
Out of service Today = if(and('Employees'[Date out of Service]<>BLANK();'Employees'[Date out of Service]<TODAY());true();FALSE())

But when I try to do the same trick for al eelected period it allways return a true when Date out of Service is not blank.
Out of service in period = if(and('Employees'[Date out of Service]<>BLANK();'Employees'[Date out of Service]<MAX('Calendar'[Date]));true();FALSE())

I'm guessing this is because of the lack of relationsship between those two, but I'm having diffeculty finding how to implement a solution. A active relation doesnt provide the solution because of the blank dates and other relations in the model. And I dont get how to implement a inactive relation in a IF measure.

Hope someone here can help me with this problem!

Koen

[v-lili6-msft · Accepted solution]

hi,@The_Coon 

      After my research, the two formulas above is calculate column, not measure. You can use this formula to add a measure instead of the column:

new Out of service in period = if(and(CALCULATE(MAX(Employees[Date out of Service])<>BLANK()),CALCULATE(MAX(Employees[Date out of Service])<MAX('Calendar'[Date]))),TRUE(),FALSE())

Result:

After I changed date

Best Regards,

Lin

[Anonymous]

Lin, you are the greatest. Worked like a charm offcourse. Thanks for helping me to start the day with a smile.

Koen