Date based conditional formatting in a Power BI Table

[Danbo45689]

Hi,

I'm relatively new to Power BI and finding my feet slowly.

I'm wanting to replicate something that is possible in Excel, and am unsure how to go about doing this in Power BI

I have a table with various values, but the important one, is the due date. I want to use conditional formatting to highlight any cell where that date is in the past. I've figured out conditional formatting for a few other functions on different Tables within Power BI but this one has me totally stuck.

In Excel, I would just highlight the range, use a formula to determine that i want to highlight any cells that contain a value prior to today, and it just works.

Could anyone share the steps i would need to take to do something like this in Power BI? Even better if it could be a 2-step format, say, Overdue by 30 days = Orange, and Overdue by more than 30 days = Red

I've included a very crude snip of how i would want it to look (from excel as my example) of both the desired outcome, and the steps i can take in Excel. I'm just stuck with how to replicate it here in a Power BI Report. Help very much appreciated!

[Anonymous · Accepted solution]

Hi @Danbo45689 ,

In PowerBI Desktop, we can get the data from that excel file, but we can't retain the original formatting.

I have created simple data.

The steps to do this are as follows:

1. Create a measure, and you can modify it to suit your needs:

Color = 
VAR _day=DATEDIFF(MAX('Table'[Date]),TODAY(),DAY)
RETURN
SWITCH(TRUE(),_day>0&&_day<=30,1,_day>30,2)

2. Conditional formatting of the table visual:

Use the value of the measure you just created as the standard:

3.Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[stewarte18]

Can you walk through the SWITCH Function and what that is doing?  
what does the expression "_day" mean/where does it come from?

I'm looking to do something similar where I highlight the row of a table if the "Date Modified" column is within the last 8 days (including today).

[Danbo45689]

Thanks for that, I'll try it out and reply with my results.