Forum Discussion
Conditional formatting of PowerBi table
I am having trouble figuring this out. I have followed instructions found elsewhere on the forum but I can't seem to get it to work.
I have a table with multiple columns, one column contains due dates which are listed in the following format (YYYYMMDD) and I want the rows to be highlighted as follows:
- Red if due date is today or has already past
- Yellow if due date is within 30 days of today
- Green if due date is greater than 31 days from today
Your help is greatly appreciated! Thanks
1. You can do this way
select each column and then apply conditional formatting with the same rule
2. you can use what color you want:
DateDueColor = if(DATEVALUE(left('Table'[DateDue],4)&"-"&MID('Table'[DateDue],5,2)&"-"& RIGHT('Table'[DateDue],2))<=TODAY(),"LIGHT BLUE",if(DATEVALUE(left('Table'[DateDue],4)&"-"&MID('Table'[DateDue],5,2)&"-"& RIGHT('Table'[DateDue],2))<=TODAY()+30,"YELLOW","#70BBFF"))3. You can remove the column and it function
3 Replies
- serpiva64
Solution Sage
Hi,
You can try this way:
creating a calculated column:
DateDueColor = if(DATEVALUE(left('Table'[DateDue],4)&"-"&MID('Table'[DateDue],5,2)&"-"& RIGHT('Table'[DateDue],2))<=TODAY(),"RED",if(DATEVALUE(left('Table'[DateDue],4)&"-"&MID('Table'[DateDue],5,2)&"-"& RIGHT('Table'[DateDue],2))<=TODAY()+30,"YELLOW","GREEN"))and then in Conditional formatting:If this post is useful to help you to solve your issue consider giving the post a thumbs up
and accepting it as a solution !
Hi there,
thanks for your response. It worked! However, three questions:
1. I would like the entire row to be conditionally formatted rather than the column - for instance, if line item one is overdue, I want that entire row in the table to be red)
2. The green color shown is too dark so how can I change the shade of green with a custom hex # instead of standard green for instance?
3. I don't want the DateDueColor column to be in my table. I now have a column with values of GREEN, YELLOW and RED. How do I remove this from the table without eliminating the formatting?
- serpiva64
1. You can do this way
select each column and then apply conditional formatting with the same rule
2. you can use what color you want:
DateDueColor = if(DATEVALUE(left('Table'[DateDue],4)&"-"&MID('Table'[DateDue],5,2)&"-"& RIGHT('Table'[DateDue],2))<=TODAY(),"LIGHT BLUE",if(DATEVALUE(left('Table'[DateDue],4)&"-"&MID('Table'[DateDue],5,2)&"-"& RIGHT('Table'[DateDue],2))<=TODAY()+30,"YELLOW","#70BBFF"))3. You can remove the column and it function