Add Measure Date Is Not In Future

[HenryJS]

Hi all,

How do I create a measure that states:

• If CandidateRef does NOT HAVE a PlacementStartDate in the future THEN YES

[v-yiruan-msft]

Hi @HenryJS ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help anyone in the community find the solution easily when they face the same problem with you. Thank you.

However, if you still have something else needs help about this thread, please provide more details, we will continue to follow up.

Best Regards

Rena

[az38]

Hi @HenryJS 

try

Measure = 
var _isDateInFuture = CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[CandidateRef]), 'Table'[PlacementStartDate] > TODAY())
RETURN
IF(_isDateInFuture < 1, "Yes", "No")

[HenryJS]

Hi @az38 

Doesn't seemed to have worked. Top two CandidateRef's have a PlacementStartDate's in the future.

It needs to be if the CandidateRef has a row where PlacementStartDate || ExtensionStartDate is in the future THEN YES for all rows with that CandidateRef

Hope that makes sense

[v-yiruan-msft]

Hi @HenryJS ,

What's your actual expected result? Which situation the field Finisher will display "Yes"? Situation 1 or situation 2?

1. When there is No PlacementStartDate in future for that CandidateRef?

2. When PlacementStartDate or PlacementEndDate have dates in future?

For example, the field Finisher will display yes or no for the CandidateRef 69785 ,105468 and 24768?

Best Regards

Rena

[az38]

@HenryJS 

you asked

---

@HenryJS wrote:

If CandidateRef does NOT HAVE a PlacementStartDate in the future THEN YES

---

So, if you want YES when CandidateRef has a future try

Measure = 
var _isDateInFuture = CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[CandidateRef]), 'Table'[PlacementStartDate] > TODAY())
RETURN
IF(_isDateInFuture > 0, "Yes", "No")