Building a Table to show Projects that are Ahead, on-Time or Behind schedule

[kamini]

Hi, thanks in advance for your time.

I have to enhance an existing Table in Power BI to show Projects that are Ahead, on-Time or Behind schedule. The existing Table includes the following information,

Name	Start date	Due Date	Finish date	Project Progress	On-Time	Late	Ahead
Project 1	2/14/2024	9/14/2024	9/14/2024	100%	Yes	No	No
Project 2	9/14/2024	9/23/2024		90%	No	No	Yes
Project 3	03/19/2023	3/12/2024	05/19/2023	20%	No	Yes	No

For the most part, I have the start date, Finish Date, and Due Dates and Progress but the main request is to obtain some code and/or recommendation to designate a symbol or color using Conditional Formatting on the field. The On-Time, Late, or Ahead can be a part of a single Column as a drop down list. 

Thanks for your time. 

[Selva-Salimi · Accepted solution]

Hi @kamini 

it is not that much clear for me what your expectation is. but based on my understanding, I think that you need to show the project situation in one column then it can be easy to conditional formatting. I recommend to write another column beside these columns using "if" or "switch" , (if ( your_table [On-time]= "yes'", "On-time" , if ( your_table [late]="yes" , "Late" , if (your_table [ahead] = "yes" , "ahead"))).

if this is not what you expect please clarify by an example.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.  

[kamini]

Thanks so much and your recommendation was amazing. I'm facing two issues and if you want, I can open a new request. The Green Text showing and the Progress column showing info the left of Axis. 

1) I can't remove the actual title (Green) and just show the color green without the text.

Here is the code that creates the Result Column showing Green text (To remove it somehow). 

Result = SWITCH(True(),[Finish Date] > [Due Date] && 'ITD Projects'[Status]="Completed","Red",[Finish Date] <[Due Date] && 'ITD Projects'[Status]="Completed","Green",[Finish Date] =[Due Date] && 'ITD Projects'[Status]="Completed" ,"Blue")

2) One Progrss columns looks great but the column is split before the Axis and after the Axis line while I don't need to show the Axis line and just show positive results.  

Progress =

VAR DaysSinceStart = DATEDIFF([Start date], TODAY(), DAY)

VAR TotalDuration = DATEDIFF([Start date], 'ITD Projects'[Due Date], DAY)

VAR ProgressValue = DIVIDE(DaysSinceStart, TotalDuration, 0)

RETURN IF(TODAY() > 'ITD Projects'[Due Date], 1, ProgressValue)

and the Databar and Background Setting

Screen shot of the report,

Thank you so much for your time.

[kamini]

Sorry for the unclarity. Here is the code I have for showing Progress. 

Progress = VAR DaysSinceStart = DATEDIFF([Start Date], TODAY(), DAY) VAR TotalDuration = DATEDIFF([Start Date], [Finish Date], DAY) VAR ProgressValue = DIVIDE(DaysSinceStart, TotalDuration, 0) RETURN IF(TODAY() > [Finish Date], 1, ProgressValue)

The above shows the Progress for each project but now if you could share a sample code that shows a Project based on it's start date, finish date, and due date (When the project was due), I would greatly appreciate it. 

Thanks,