Forum Discussion
Conditional Formatting Dates for color background
Hello All and Happy New Years,
I'm hoping to achieve the following results with conditional formatting dates. I have two columns of dates "Start Date" and "End Date". For each column i would like to format the background to yield this color result
If "Start Date" is today or pass date then color is RED
If "Start Date" is within 14 days of today then Orange
If "Start Date" is within 30 days of today then Yellow, everthing else as is.
I was able to achieve the result with Excel using the following formula to switch to numeric, but I want to get away from Excel formula and use DAX when possible and also to see if calculated columns or measure would be the better method to this.
Excel formula = =IF([@[Req. start]]-TODAY()<=1,"1",IF(AND([@[Req. start]]-TODAY()>1,[@[Req. start]]-TODAY()<15),"2",IF(AND([@[Req. start]]-TODAY()>=15,[@[Req. start]]-TODAY()<31),"3","4")))
Thank you, All!
Anonymous create another measure for the font
Foregound Color = VAR Days = DATEDIFF(TODAY(),MAX([Required Start]), Day) RETURN IF ( Days > 14, "BLACK" )or
Foregound Color = IF ( [Req Start Color] = "Yellow", "Black" )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
4 Replies
Anonymous to use the color in the conditional formatting, you need to use measure, you can use something like this:
Color Measure = VAR __days = DATEDIFF ( MAX ( Yourtable[StartDate] ), TODAY(), DAY ) RETURN SWITCH ( TRUE(), __days > 30, BLANK(), __days > 14, "Yellow", __days > 1, "Orange", "Red" )
Tweak the formula as you see fit.✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thanks. This worked with a minor adjustment with Today() then Max([Required Start].
Now let me throw a wrench with another requirement. At the moment the yellow background has white fonts, which is the standard color for this Matrix visual, but it is impossible to read due to the yellow background. How can i change the font color for just the yellow background to black fonts?
Req Start Color =VAR Days = DATEDIFF(TODAY(),MAX([Required Start]), Day)RETURNSWITCH( TRUE(),Days > 30, BLANK(),Days > 14, "YELLOW",Days > 1, "Orange","Red")
Hi, Anonymous
You can check this one:
Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.
Appreciate your Kudos !!!
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
Anonymous create another measure for the font
Foregound Color = VAR Days = DATEDIFF(TODAY(),MAX([Required Start]), Day) RETURN IF ( Days > 14, "BLACK" )or
Foregound Color = IF ( [Req Start Color] = "Yellow", "Black" )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
