Forum Discussion
Find Difference between Measure Values and Column Values
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?
Hi kevinsray,
You could try below measures:
color = IF ( DATEDIFF ( MAX ( test[LastDone] ), TODAY (), DAY ) > MIN ( test[MaxDays] ), "red", "green" )Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi kevinsray,
You could try below measures:
color = IF ( DATEDIFF ( MAX ( test[LastDone] ), TODAY (), DAY ) > MIN ( test[MaxDays] ), "red", "green" )Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Very nice.
However, when I tried to setup the formatting, it wouldn`t let me select color in the rulesHi 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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
