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

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

Accepted Solutions
Highlighted

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
Highlighted
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





Highlighted

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

Highlighted

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

Highlighted

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

I only get the words.

 

cond form3.JPG

Highlighted

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





Highlighted

Thanks Miguel! It worked!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors