DAX for Progress of Time Based on Dynamic Status of Project

[BlueTech]

Looking for some help with progress.

Have a table called 'Project' which tracks the overall progress of a number of projects, and if those tasks are completing on a pace that matches the date (today).  My challenge is, that if a project is terminated, then I do not want the progress to continue running and to show if the project was on track at the time of termination.

I have a series of calculated columns [Week1Completed] through [Week6Completed] that I use to set the pace of the project, based on today's date.  Using a series of measures such as : WeekOneCompleted = IF(Project[DayStart]>=7,1,0) and then +14, +21 etc for each week column.  Take the values for each 'Week Completed' column and sum them and divide by 6 (as there are 6 equal parts to each project).  Then I compare that to the actual progress, based on the project to see if I am on track or not.  If time elapsed is >1, and pace is <1 then its not on track for completion and thus is marked as such.

All of this works perfectly as intended.

What I am trying to solve, is if a project is terminated midway, how can I stop the progress calculation for ONLY that particular project and none of the others?  Looking to update the calculated column [ProjectProgress] to stop figuring the number of days from today and switch to the length of time between the project start date [daystart] and the term date [termdate] *if applicable* to capture the snapshot of time that the project was being worked on.  This way the user is able to see that the project was on pace at the time it was terminated.  Allows the user to see trends on if a project was behind schedule and was terminated for that reason, or if certain project managers have this occur more than others... etc.

Expected results if viewed as of today

[DayStart]	[WeekOneCompleted]	[WeekTwoCompleted]	[WeekThreeCompleted]	[WeekFourCompleted]	[WeekFiveCompleted]	[WeekSixCompleted]	[TermDate]	[ProjectProgress]
10/4/2021	1	1	1	0	0	0		50%
10/4/2021	1	0	0	0	0	0	10/8/2021	16.60%
10/11/2021	1	1	0	0	0	0		33.20%
10/18/2021	1	1	0	0	0	0		33.20%

[v-jingzhang · Accepted solution]

Hi @BlueTech 

Assume you use Today() in all of your DAX expressions to calculate the length between DayStart and today (base date), you just need to replace Today() with [TermDate] if it's a terminated project. 

For example, if your original column is like below

old column =
VAR dateLength = DATEDIFF ( Project[DayStart], TODAY (), DAY )
RETURN
    IF ( dateLength >= 7, 1, 0 )

You could add an IF condition to determine the base date by whether it's a terminted project. 

new column =
VAR baseDate = IF ( ISBLANK ( Project[TermDate] ), TODAY (), Project[TermDate] )
VAR dateLength = DATEDIFF ( Project[DayStart], baseDate, DAY )
RETURN
    IF ( dateLength >= 7, 1, 0 )

Hope this helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.