cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Help with Date Conditional Formatting (Due Date)

Hello!

 

I need help in using conditional formatting for dates.

I have a column for due dates and would want a color coded value when it's far from due date, almost due date and beyond due date.

 

For example.

I want Task Number and Due date background turned GREEN if it's far from it's due date.

ORANGE if it's close to its due date and

RED if it's beyond its due date.

 

cond form.JPG

1 ACCEPTED SOLUTION

Hi @swaggerboy69 ,

 

This is a measure and you should replace the ";" by ",".

 

CondittionalFormatting =
VAR number_of_days =
    SELECTEDVALUE ( 'Table'[Due Date] ) - TODAY ()
RETURN
    SWITCH (
        TRUE (),
        number_of_days < 0, "Red",
        number_of_days < 10, "Orange",
        "Green"
    )

 

The number_of_days is a variable is calculated on top as you can see tha make the difference between the due date and today.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

6 REPLIES 6
Super User III
Super User III

Hi @swaggerboy69 ,

 

What is the condition to have it Close/Far from due date?

 

If you create a measure similar to the one below you can have the desired result:

 

CondittionalFormatting =
VAR number_of_days =
    SELECTEDVALUE ( 'Table'[Due Date] ) - TODAY ()
RETURN
    SWITCH (
        TRUE ();
        number_of_days < 0; "Red";
        number_of_days < 10; "Orange";
        "Green"
    )

Just adjust the 10 days to whatever value you need then use it as your condittional formatting:

 

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Hello! Thank you for your response.

Should I just add a measure?

Where do I get number_of_days?

I get red lines. Sorry I'm, not too good with codes.

 

cond form2.JPG

Hi @swaggerboy69 ,

 

This is a measure and you should replace the ";" by ",".

 

CondittionalFormatting =
VAR number_of_days =
    SELECTEDVALUE ( 'Table'[Due Date] ) - TODAY ()
RETURN
    SWITCH (
        TRUE (),
        number_of_days < 0, "Red",
        number_of_days < 10, "Orange",
        "Green"
    )

 

The number_of_days is a variable is calculated on top as you can see tha make the difference between the due date and today.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Great. That worked. However how do I apply it to the table visual?

I only get the words.

 

cond form3.JPG

Hi @swaggerboy69 ,

 

You need to select the dates and task number and all the columns you need  then slected the condittional formatting and aplly the value of the measure see images below:

 

MFelix_0-1603976350304.png

MFelix_1-1603976419332.png

MFelix_2-1603976432578.png

MFelix_3-1603976441560.png

 

MFelix_4-1603976459607.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Thanks Miguel! It worked!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors