Conditional formatting using two date columns

hawkeye159 — Wed, 19 Jun 2024 12:04:01 GMT

Hi all, I'm new here and quite new to Power BI, trying to learn as I go and using forums like this to pick up tips.

But I'm struggling to find a solution that matches my problem.

I have a table with two different target dates - a normal target date (Target Date) and an extended target date (EOT Date). The EOT date won't always be populated but the Target Date will. I'm wanting to add conditional formatting so that, if the target date has expired, it will show red, BUT, if the EOT date has not expired, it will still show green.

I've tried adding a measure to show the Target Date as red where the date has passed and green if not:

Measure - Over Target Date = IF(SELECTEDVALUE(DCAPPL[TARGET DATE])>TODAY(),1,0)

Then added conditional formatting:

That seemed to work:

(Note: I added the same conditional formatting to the Application No. data too).

But am I able to highlight the columns if the EOT date was still in time? So in the below screenshot, the application highlighted has a target date that has expired but the EOT date is still within time. I would like this row to be showing as Green as it is still within time. Likewise, the two applications below the highlighted one would show Red as their EOT date has expired.

My apologies if this post is too lengthy or not concise enough. I hope it makes sense and someone out there is able to understand me and help!

Many thanks in advance!

Re: Conditional formatting using two date columns

aduguid — Wed, 19 Jun 2024 13:39:46 GMT

Create a new calculated column or measure

Status = IF( ISBLANK('Table'[EOT Date]), IF('Table'[Target Date] < TODAY(), "Expired", "Not Expired"), IF('Table'[EOT Date] < TODAY(), "Expired", "Not Expired") )

Set the rules for the conditional formatting:

If Status is "Expired", set the color to red.
If Status is "Not Expired", set the color to green.

Re: Conditional formatting using two date columns

hawkeye159 — Thu, 20 Jun 2024 19:30:59 GMT

Thank you so much, that works for me!

topic Re: Conditional formatting using two date columns in DAX Commands and Tips

Conditional formatting using two date columns

Re: Conditional formatting using two date columns

Re: Conditional formatting using two date columns