The basic formula for the Earned Value column is very straightforward. Earned Value is simply the % Complete of a task multiplied by the Planned Value of the same task.
Project managers often like to see the cumulative earned value of a project displayed over the course of time. This is the purpose of the EV measure. To explain how this measure operates, we start by specifying the current reporting date, __ReportingDate. We then get the maximum Finish_Date within the R03_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 earned value for each date, we construct a table variable, __Table, by first using FILTER to return only those rows within the R03_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 R03_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 R03_Project table and the current date, __Date.
Now, we use ADDCOLUMNS once more to add the __EV column. The calculation for __EV 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 duration in days for the task, __TaskDaysDuration. If so, then the full Earned 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 Earned Value of the task-based upon a ratio of how many days of the task duration have been accrued.
Finally, we simply need to return the sum of our calculated earned values in the __EV column using SUMX in order to return the cumulative earned value of the project at any particular date. However, we first check if __Date exceeds our __ReportingDate and, if so, we return BLANK since we do not wish to display __EV values for dates exceeding our current __ReportingDate.