Datediff with a Filter based on a Slicer

[JB]

I am trying to make a RAG traffic light indicator that shows when a particular Contract was last updated with new information (within the last week = green, within last two weeks = amber, over two weeks ago = red). 

To do this I am using a field value to dictate the fill colour of a circle. It works, however it always shows green as it takes the most recent entry from the whole dataset, ignoring the Slicer that selects one Contract at a time. 

This Filter function turns the Slicer's current selection into a value.

Contract select = SELECTEDVALUE(Revised[Contract])

How do I include it within the following traffic light function?

Contract RAG lights = 
var _dt = datediff(TODAY(), max(Revised[Week Ending]), day)
return
Switch( TRUE(),
_dt <= 7, "light green",
_dt <= 14, "orange",
_dt >= 15, "red",
"blue")

I tried inserting this line into the above but had no success. 

FILTER(Revised, Revised[Contract] = [Contract select])

Thanks for your help.

[ValtteriN]

Hi,

Try something like this:

Contract RAG lights = 
var _dt = CALCULATE(datediff(TODAY(), max(Revised[Week Ending]), day),ALL(Revised),Revised[Contract]=[Selected contract])
return
Switch( TRUE(),
_dt <= 7, "light green",
_dt <= 14, "orange",
_dt >= 15, "red",
"blue")

[JB]

Hi, thank you for your reply.

I did that and got the following error message about the Switch function:

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 Any thoughts?

[ValtteriN]

@JB 

This happens when a measure is placed to a filter. So instead of [Selected contract] you could try using Revised[contract] = SELECTEDVALUE(Revised[contract]) directly in the calculate.

[JB]

Thanks for your response.

The following does not produce any error messages, but it also does not change the colours according to the Slicer; the original problem persists. 

Contract RAG lights = 
var _dt = CALCULATE(datediff(TODAY(), max(Revised[Week Ending]), day), Revised[contract] = SELECTEDVALUE(Revised[contract]))
return
Switch( TRUE(),
_dt <= 7, "light green",
_dt <= 14, "orange",
_dt >= 15, "red",
"blue")

 Any additional advice would be appreciated.

[ValtteriN · Accepted solution]

Hi @JB ,

Try including ALL here: 

var _dt = CALCULATE(datediff(TODAY(), max(Revised[Week Ending]), day),all(Revised), Revised[contract] = SELECTEDVALUE(Revised[contract]))

return

Switch( TRUE(),

_dt <= 7, "light green",

_dt <= 14, "orange",

_dt >= 15, "red",

"blue")

Also pay attention to the datediff when comparing values in the past. For example DATEDIFF(TODAY(), 1.1.2022,DAY) will be negative. In my tests the above formula returned different values for different contracts.

[JB]

Datediff was the issue, I didn't realise days in the past were presented as negative values. My original code works now, without needing to filter.

Contract RAG lights = 
var _dt = datediff(TODAY(), max(Revised[Week Ending]), day)
return
Switch( TRUE(),
_dt >= -7, "light green",
_dt >= -14, "orange",
_dt <= -15, "red",
"blue")

 Thanks for your help.