Forum Discussion
Solved
PowerBI Measure for Colouring Date Ranges
Hi, I'd appreciate some help with the following measure. I'm looking to colour the date value as follows: Red - Target Date has passed - Value "2" Amber - Target Date is due withing 5 days - Val...
Days that are in the future will have a negative difference with today, try
Colour = VAR _day = DATEDIFF ( MAX ( 'Sheet1'[Target Date] ), TODAY (), DAY ) RETURN SWITCH ( TRUE (), _day <= 0 && _day >= -5, 1, _day < -5, 0, _day > 0, 2 )
You need to change the order in the SWITCH logic - when _day is greater than 0 and less than 5, which you want to be amber, it is still triggering the clause where _day is greater than 0 for red. Try
Colour =
VAR _day =
DATEDIFF ( MAX ( 'Sheet1'[Target Date] ), TODAY (), DAY )
RETURN
SWITCH ( TRUE (), _day >= 0 && _day <= 5, 1, _day < 5, 0, _day > 0, 2 )
Thank you so much for your quick response.
I've applied this formula which gets me slightly further, however, the amber is the wrong side of the target date. It needs to be red up to 21/08/2025, amber from 22/08/2025 (today) to 26/08/2025 and green from 27/08/2025 going forward.
It appears that the greater than and less than is working in the opposite direction for some reason?