topic Re: Datediff with a Filter based on a Slicer in DAX Commands and Tips

Datediff with a Filter based on a Slicer

JB — Wed, 02 Feb 2022 13:40:20 GMT

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.

Re: Datediff with a Filter based on a Slicer

ValtteriN — Wed, 02 Feb 2022 15:48:30 GMT

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")

Re: Datediff with a Filter based on a Slicer

JB — Wed, 02 Feb 2022 16:08:00 GMT

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?

Re: Datediff with a Filter based on a Slicer

ValtteriN — Wed, 02 Feb 2022 16:35:23 GMT

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.

Re: Datediff with a Filter based on a Slicer

JB — Thu, 03 Feb 2022 10:09:13 GMT

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.

Re: Datediff with a Filter based on a Slicer

ValtteriN — Thu, 03 Feb 2022 11:38:04 GMT

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.

Re: Datediff with a Filter based on a Slicer

JB — Thu, 03 Feb 2022 12:43:13 GMT

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.