The SV_PV measure represents the cumulative planned value of the project over time. For the SV_PV measure, we start by simply getting the maximum Finish_Date within the R05_Project table, __MaxDate, and the maximum date within the current context of our line chart visualization, __Date.
Next, since we wish to display the cumulative sum of the planned value for each date we construct a table variable, __Table, by using FILTER to return only those rows within the R05_Project table that have a Start_Date that is less than our current date, __Date. We add two columns to this base table using ADDCOLUMNS. The first column, __TaskDaysDuration, uses DATEDIFF to calculate the total duration in days of each task (row) within the filtered R05_Project table. This duration is the number of days between the Start_Date and Finish_Date of each task. The second column, __Days, again uses DATEDIFF but this time calculates the total duration in days between the Start_Date of each task (row) within the filtered R05_Project table and the current date, __Date.
Now, we use ADDCOLUMNS once more to add the __PV column. The calculation for __PV consists of two nested IF statements. The first IF statement checks if the current date, __Date, is greater than the maximum date within our project plan, __MaxDate. If so, then BLANK is returned. This prevents extraneous values from being displayed in the event that the dates in our calendar table exceed the end of the project. If the current date, __Date, is not greater than the maximum date within our project plan, __MaxDate, then our second IF statement executes. This IF statement checks to see if the number of days between the Start_Date of a task and the current date, __Days, is greater than or equal to the total number of days the task lasted, __TaskDaysDuration. If so, then the full Planned Value amount for the task is returned. However, if the number of days between the Start_Date of a task and the current date, __Days, is not greater than or equal to the total duration in days for the task, __TaskDaysDuration, then we apportion the Planned Value of the task based upon a ratio bases on how many days of the task duration have been accrued.
Finally, we simply need to return the sum of our calculated planned values in the __PV column using SUMX in order to return the cumulative planned value of the project at any particular date.
The SV_EV measure represents the cumulative earned value of the project over time. The SV_EV measure is nearly identical to the SV_PV measure. The exception is that, at the start of the formula, we specify a reporting date, __ReportingDate. Then, in the RETURN statement, we check to see if __Date is greater than __ReportingDate and, if so, we return BLANK since we do not want to report SV_EV values that are past our __ReportingDate.
The SV$ measure is the scheduled variable of the project measured in terms of cost. The calculation for SV$ is simply subtracting the SV_EV measure from the SV_PV measure. However, first, we check to see if SV_EV is BLANK and, if so, return BLANK.
To get the current value of the schedule variance in terms of cost, Current SV$, we start by creating a table variable, __Table, that contains all of the dates from our R05_Calendar table and use ADDCOLUMNS to add a column called __SV$. The value for the __SV$ column is simply the SV$ measure. Then, we find the maximum date where __SV$ is not blank using MAXX and FILTER. This is the most current date for SV$. Thus, we can simply FILTER our __Table to this specific date and return the value in the __SV$ column.
The SVDays measure provides the schedule variance between the baseline project schedule and the current state of the project in terms of the number of days the project is ahead or behind the baseline project schedule. This is done by creating a table variable, __Table, that contains all of the dates from our R05_Calendar table and then adding two columns, __PV and __EV. We use the ADDCOLUMNS function to add both the __PV and __EV columns. The values for these columns are simply our SV_PV and SV_EV measures, respectively. Then, we use __Table to find the maximum value of the Date column where the __EV column is not BLANK; that is, __EVDate. In addition, we use __Table to find the maximum value of the __EV column, that is, __CurrentEV. Since our SV_EV measure is cumulative, we are assured that this value corresponds to the __EVDate value calculated on the previous line.
Now, we want to find the corresponding date for SV_PV where the value for SV_PV is the same as, or as close as possible to, the current earned value, __CurrentEV. To accomplish this, we add an additional column to __Table called __Diff using ADDCOLUMNS. This new version of the table is called __Table1. The value for the __Diff column is the absolute value of each row's __PV column, minus the __CurrentEV variable. Now, we can determine the minimum value of the __Diff column, __MinDiff, by using MINX with __Table1 as the first parameter.
Once we have a value for __MinDiff, we can use this value to look up the date in the table that corresponds with this value. This is the date where the __PV column closely matches __CurrentEV. Then, we simply need to return the number of days between these two dates using DATEDIFF.