Measures: FirstSortedValue(DEPT, -POSITION_DT) and Date(Max(POSITION_DT))
You could also do this by suppressing zero/null values and picking only the latest line in a measure if for some reason you don't want to use measures.
if I select my CalDate as 09/19/2018 then below records are populating which is correct:
ID DEPT DEPT_TYPE PosDate Count
100 Account AB 06/01/2021 1
100 Finance AB 03/29/2008 0
100 Finance AB 12/25/2011 0
100 Finance AB 01/01/2018 0
Now here you can see ID-100 was working in multiple DEPT with different PosDate and as per current logic its calculating count as 1 for max of total PosDate.
Expetation is when user select Caldate as 09/19/2018 then during this period ID was working in Finanance DEPT and its max posDate is 01/01/2018 so this record needs to shown in count as 1.
expected result:
ID DEPT DEPT_TYPE PosDate Count
100 Account AB 06/01/2021 - <---- this record can be null or removed as Caldate is backdated date selected
100 Finance AB 03/29/2008 0
100 Finance AB 12/25/2011 0
100 Finance AB 01/01/2018 1 <--- this needs to shown only for backdated calDate with max posDate
