Find Difference between Measure Values and Column Values

[kevinsray]

OK, so I am trying to get some conditional formatting happening, but it is quite difficult.

We have a report which shows as follows

Task        Frequency LastDone
Task 1     Yearly        1/1/2018
Task 2     Yearly        1/1/2019
Task 3     Monthly     1/8/2019
Task 4     Monthly     1/6/2019

What I am trying to achieve is highlight the LastDone date field if it is out of range.
I have a measure to get the datediff. elapsed = DATEDIFF(MAX(LASTDONE),TODAY(),DAY)
I have a column which puts the maxdays using SWITCH. MaxDays = SWITCH(FREQUENCY,"Yearly","365","3 Monthly","90","6 Monthly","180","Monthly","30","Weekly","7","Fortnightly","14","Bi Monthly","60","Daily","1")

What I am trying to do is if elapsed is more than MaxDays, then format the LASTDONE field to red background.
Any thoughts on how I can work this with a measure and column? 
Or is there another way that I am not seeing?

[dax · Accepted solution]

Hi kevinsray,

You could try below measures:

color =
IF (
    DATEDIFF ( MAX ( test[LastDone] ), TODAY (), DAY ) > MIN ( test[MaxDays] ),
    "red",
    "green"
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kevinsray]

Very nice. 
However, when I tried to setup the formatting, it wouldn`t let me select color in the rules

[dax]

Hi kevinsray,

You could see I use Field Value when set conditional format, I have set color in measure. If you want to change color you could use "#50C7C7" to replace "red" or "green". You could get color code in color picker(https://htmlcolors.com/google-color-picker)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kevinsray]

Hi again.
I figured it out.
I had the data type for the MaxDays as text. doh....

changed to decimal and now it works as expected.
THANK YOU VERY MUCH.

[kevinsray]

Or at least I thought it worked.
works on some fields, but not others.

[kevinsray]

Yes, sorry.
That is what I meant.
Would there be a reason why I could not select the color measure in the field value