Forum Discussion
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 - Value "1"
Green - Target Date is due over 5 days - Value "0"
The rule below works for the red, however, the amber value is only showing for todays date, rather than all dates due within 5 days.
I'd really appreciate any help with this.
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 )
5 Replies
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 )Amazing, that worked perfectly. Thank you so much for your help.
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?