Help with distinct count with date conditions

[yve214]

Hi there,

ID	Emp_ID	Interval	Effective_Date	Score
2	1001	0	8/26/2020	3.3
6	1002	0	1/1/2022	2.0
3	1001	3	11/20/2020	3.5
5	1001	5	2/1/2021	4.1
6	1002	5	09/05/2021	5.0

I am trying to "count the emp IDs at interval 5, making sure the dates at that interval of 5 is greater than the dates at interval 0 like (effective date at the 5 interval > the effective date at interval 0). please can someone help with this?

Here is how i approached it. I created two date measures for both interval 0 and interval 5. then I did an

if((date_at_interval_5) > (date_at_interval_0) && table[interval] = 5, distinctcount(table[emp_ID]). But i keep getting blank.

Results:

In this case, I should only expect a return of 1 count for emp 1001 since they have a 5 month interval with a date greater than their 0 interval. Since 1002 had a 5 interval of an earlier date 09/05/2021 at 5 < 1/1/2022 at 0 , this should be excluded.

[amitchandak · Accepted solution]

@yve214 , Create a measure like

measure =
var _max0 = maxx(filter(allselected(Table), Table[Emp_id] = max(Table[Emp_id]) && Table[Interval] =0),Table[Effective_Date])
var _max5 = maxx(filter(allselected(Table), Table[Emp_id] = max(Table[Emp_id]) && Table[Interval] =5),Table[Effective_Date])
return
calculate(distinctcount(table[emp_ID]), filter(Table, Table[Interval] =5 && _max5>_max0))

[yve214]

@amitchandak Thank you very much. I was able to get the numbers in a row context. Please how can i get this number in a card?