Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Conditional Formatting for a date based on another date

Hello,

 

I need a way to highlight a date in a table based on it being before or after the first date. All the data comes from project.

 

Task finish date   task baseline date

18/01/2022           19/01/2022

 

So in this example, task finish date would be green. 

 

Thanks

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@Anonymous ask your boss to give the requirement in one go, or hire som consultant/training for you 🙂

 

Color = 
VAR __finishDate = MAX ( Highlight[Finish Date] )
VAR __baseDate = MAX ( Highlight[Base Date] )
VAR __days =  DATEDIFF ( __finishDate, __baseDate, DAY )
RETURN

SWITCH ( TRUE(),

    __days > 60, "Green",
    __days > 0, "Orange",
    "Red"
)

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@Anonymous I know but in case if he ever agreed, feel free to reach out since I do training programs. Cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Anonymous ask your boss to give the requirement in one go, or hire som consultant/training for you 🙂

 

Color = 
VAR __finishDate = MAX ( Highlight[Finish Date] )
VAR __baseDate = MAX ( Highlight[Base Date] )
VAR __days =  DATEDIFF ( __finishDate, __baseDate, DAY )
RETURN

SWITCH ( TRUE(),

    __days > 60, "Green",
    __days > 0, "Orange",
    "Red"
)

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thank you very much, worked great. Tell me about it with the requirements and training ha

parry2k
Super User
Super User

@Anonymous add a measure:

 

 

Color = IF ( MAX ( Table[FinishDate] ) < MAX(Table[BaseDate]), "Green", "Red" )

 

 

Then on Finish Date column, go to conditional formatting, and set the formatting to field value and use above measure.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thank you very much. Another quick question, my boss has asked for the following:

 

Within 60 calendar days = Amber

Greater than 60 days = Green

 

So is it possible to change the formula to reflect those. Essentially if the finish date is up to 60 days before the baseline date it's amber. If it's greater than 60 days it's green and if the finish date is after the baseline date it's red. So he's asked for some additional stuff.

 

Your help is apprieciated and thank you  

 

parry2k
Super User
Super User

@Anonymous what is the first date? Your example is not clear. are you saying if the finish date is before the baseline date then highlight it in green?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Yes sorry the task finish date is the first date. If it is before the baseline date make it green, if it's after make it red.

 

Thanks

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.