Calculate using filter

[Anonymous]

i have two tables 

First table has a date where citations were issued.

Second table has a date where officer was on a certain schedule.

The tables are joined by a unique ibm # 

i am trying to filter the data to show what shift the officer wrote the tickets on. I can get total citations written by the officer but if that officer works on a different shift it also produces the total for that shift as a total of the officer

ibm	offense_date	citation_number
jj220	01/01/2022	1
jj220	01/01/2022	2
jj220	01/02/2022	5
aa420	01/01/2022	3
aa420	01/01/2022	4
aa420	01/02/2022	6

ibm	shift	shift start date
jj220	a shift	01/01/2022
jj220	a shift	01/02/2022
aa420	b shift	01/01/2022
aa420	t shift	01/02/2022

[johnt75 · Accepted solution]

Create a calculated column on the Citation table

Shift = 
var offenseDate = Citations[Offense date]
return SELECTCOLUMNS( TOPN( 1, 
   FILTER( RELATEDTABLE( Shifts ), Shifts[Start date] <= offenseDate ),
   Shifts[Start date], DESC ),
   "@shift", Shifts[Shift]
)

[Anonymous]

Thank you that works