Conditional formatting of date based on filter value

[metcala]

Hi

I'm just after some advice on how I can highlight rows which have not had comments in red and not had comments in the last two weeks in amber.

Data Structure

Status

Reporting Month	ID	Team	Site	Completion Target
30/4/23	1001	A	W	30/6/23
30/4/23	1002	A	X	31/8/23
30/4/23	1003	B	Y	30/9/23
31/5/23	1001	A	W	30/6/23
31/5/23	1002	A	X	31/8/23
31/5/23	1003	B	Y	30/9/23
31/5/23	1004	A	Z	31/10/23

Update

ID	Update Date	Update
1001	28/2/23	On target
1001	28/5/23	On target
1002	1/5/23	Behind schedule by 4 weeks
1004	20/5/23	On target

Expected Outcome

Slicer - Reporting Month 31/5/23

ID			Team	Site	Completion Target
1001
	28/2/23
		On target	A	W	30/6/23
	28/5/23
		On target	A	W	30/6/23
1002
	1/5/23
		Behind schedule by 4 weeks	A	X	31/8/23
1003			B	Y	30/9/23
1004
	20/5/23
		On target	A	Z	31/10/23

Rows set as ID, Update Date and Update so can be collapsed for summarised view.

When I go on Team > Conditional Formatting and select Update Date as the field the only option showing is Count. Not sure if I need to add a measure for calculating the difference between max Update Date and Reporting Date selected in the slicer.

Hopefully this makes sense and any pointers in the right direction would be very much appreciated!

Thanks in advance.

[metcala · Accepted solution]

Hi

I have just resolved this with the following DAX measure

Days since update =
VAR SelectedDate = MAX('Status'[Reporting Month])

RETURN
   DATEDIFF(

      CALCULATE(

         MAX('Update'[Update Date]), ALLEXCEPT('Status','Status'[ID])),

         SelectedDate,

         DAY

   )

)

Hope this is of use to someone.