how to Show dates in traffic light signals

[krrish116]

Hi Everyone,

I do have a table with date column and customer column and traffic signal indicator column.

I have created the traffic light indicator column with a measure "Indicator = UNICHAR(11044)"

I have filtered the date column to show only next 90 days with Relative Date Filter from today. so it will show only dates from today to next 90 days.

i have to show 1-30 days with Red Indicator

i have to show 31 -60 days with yellow indicator

i have to show 61-90 days with green indicator.

    Date                    Customer                Indicator

02/01/2021              XXXXX                     Red 

18/01/2021              dhskdn                    Red

29/01/2021              sjdjdm                     Red

06/02/2021              jshfjdf                      Yellow

24/02/2021              jjnm m                     Yellow

22/03/2021              jjndjw                       Green

26/03/2021             uhuuhu                     Green

Please help me in doing this and everyday the dates are going to change and it will display on next days in this Table..

Please help me @amitchandak 

Thanks,

Krishh.

[amitchandak · Accepted solution]

@krrish116 , My bad, Reverse the order, Bigger date should be second

indicator=
var _dt = datediff(today(), max(Table[Date]), day)
return
Switch( true() ,
_dt <= 30, "red",
_dt <= 60, "Yellow",
_dt <= 90, "green",
"green")

[krrish116]

Hi @amitchandak ,

Hatsoff to your intellectual brain sir. Thankyou so much.

you are the best sir.

Thanks,

Krish..

[amitchandak]

@krrish116 , You have use unichar and this color measure , in conditional formatting with field value option.

You can also use icon formatting in matrix and table

measure =
var _dt = datediff(max(Table[Date]), today(), day)
return
Switch( true() ,
_dt <= 30, "red",
_dt <= 60, "Yellow",
_dt <= 90, "green",
"green")

Refer these two

https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

[krrish116]

Hi @amitchandak ,

it is showing only red in the table when i put that measure in my table.

indicator=
var _dt = datediff(max(Table[Date]), today(), day)
return
Switch( true() ,
_dt <= 30, "red",
_dt <= 60, "Yellow",
_dt <= 90, "green",
"green")

    Date                    Customer                Indicator

02/01/2021              XXXXX                     red

18/01/2021              dhskdn                    red

29/01/2021              sjdjdm                     red

06/02/2021              jshfjdf                      red

24/02/2021              jjnm m                    red

22/03/2021              jjndjw                      red

Please help me to sort it out.

Thanks,

Krish..