Forum Discussion
Trying to write a Measure that projects future
I am trying to write a measure that uses other measures to create a projection line for a line chart. I want to add a green dotted line that starts with the Previous_Sprint and adds the Use Vel for the remaining sprints.
The first four columns below make up the chart above. It is the Projected column below that I manually created within Excel to show what I am looking for. (Previous_Sprint is a measure. Use Vel is a column in Use_Vel table.)
| Sprint | Orig EffortRT | Curr EffortRT | Effort-DoneRT | Previous_Sprint | Use Vel | Projected |
| PI2-1 | 406 | 407 | 407 | PI2-2 | 409 | |
| PI2-2 | 766 | 785 | 785 | PI2-2 | 409 | 785 |
| PI2-3 | 1097 | 1258 | 952 | PI2-2 | 409 | 1194 |
| PI2-4 | 1500 | 1769 | 952 | PI2-2 | 409 | 1603 |
| PI2-5 | 1928 | 2163 | 952 | PI2-2 | 409 | 2012 |
| PI2-6 | 2340 | 2571 | 952 | PI2-2 | 409 | 2421 |
| PI2-7 | 2761 | 2961 | 952 | PI2-2 | 409 | 2830 |
Also, I would like to know if there is a way to not display the redundant data for the Effort-DoneRT field. (This is a running total and since we have not started PI2-4 and beyond, all values are the same.)
I was able to figure out a way to do both by myself. May not be efficient, but it works.
1) First I had to create a numeric field instead of using text field. (Sprint#, Current_Sprint#)
2) Create a measure to capture points Done up to previous sprint.
Effort-Done_Prev =CALCULATE([Effort-Done],FILTER(ALL('PI_Work'[Sprint#]), ('PI_Work'[Sprint#] < [Current_Sprint#])))3) Create a measure to determine future projected points.TVel =CALCULATE(sum(PI_Orig[UVel]),FILTER('PI_Work','PI_Work'[Sprint]>=[Current_Sprint]))4) Combine 2&3 together.Effort-Proj =[Effort-Done_Prev] + [TVel]5) Then I created a running total for above.Effort-ProjRT =CALCULATE([Effort-Proj],FILTER(ALLSELECTED('PI_Ref'[Sprint]),ISONORAFTER('PI_Ref'[Sprint], min('PI_Ref'[Sprint]), DESC)))6) I created another measure that includes points only up to the Current Sprint.Effort-Done_Curr =CALCULATE([Effort-Done],FILTER(ALL('PI_Work'[Sprint#]), ('PI_Work'[Sprint#] <= [Current_Sprint#])))7) Then I created a running total for above.Effort-Done_CurrRT =if([Effort-Done_Curr]<>Blank(),CALCULATE([Effort-Done_Curr],FILTER(ALLSELECTED('PI_Ref'[Sprint]),ISONORAFTER('PI_Ref'[Sprint], min('PI_Ref'[Sprint]), DESC))))
2 Replies
"It is the Projected column below that I manually created within Excel to show what I am looking for. "
What's the formula?
I was able to figure out a way to do both by myself. May not be efficient, but it works.
1) First I had to create a numeric field instead of using text field. (Sprint#, Current_Sprint#)
2) Create a measure to capture points Done up to previous sprint.
Effort-Done_Prev =CALCULATE([Effort-Done],FILTER(ALL('PI_Work'[Sprint#]), ('PI_Work'[Sprint#] < [Current_Sprint#])))3) Create a measure to determine future projected points.TVel =CALCULATE(sum(PI_Orig[UVel]),FILTER('PI_Work','PI_Work'[Sprint]>=[Current_Sprint]))4) Combine 2&3 together.Effort-Proj =[Effort-Done_Prev] + [TVel]5) Then I created a running total for above.Effort-ProjRT =CALCULATE([Effort-Proj],FILTER(ALLSELECTED('PI_Ref'[Sprint]),ISONORAFTER('PI_Ref'[Sprint], min('PI_Ref'[Sprint]), DESC)))6) I created another measure that includes points only up to the Current Sprint.Effort-Done_Curr =CALCULATE([Effort-Done],FILTER(ALL('PI_Work'[Sprint#]), ('PI_Work'[Sprint#] <= [Current_Sprint#])))7) Then I created a running total for above.Effort-Done_CurrRT =if([Effort-Done_Curr]<>Blank(),CALCULATE([Effort-Done_Curr],FILTER(ALLSELECTED('PI_Ref'[Sprint]),ISONORAFTER('PI_Ref'[Sprint], min('PI_Ref'[Sprint]), DESC))))
