Conditional Formatting Dates for color background

[punksterz626]

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!

[parry2k · Accepted solution]

@punksterz626 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.⚡

[ALLUREAN]

Hi, @punksterz626 

You can check this one:

https://www.dropbox.com/s/8db0k916p177llt/Conditional%20Formatting%20Dates%28allure-analytics.com%29...

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

[parry2k]

@punksterz626 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.⚡

[punksterz626]

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)

RETURN

SWITCH( TRUE(),

Days > 30, BLANK(),

Days > 14, "YELLOW",

Days > 1, "Orange",

"Red")