Forum Discussion
Helper IICreating a column (or measure) based on the difference in values from another column
I am trying to create a column based on the difference of values in another column (maybe measure if column is the wrong approach). As you can see from the data below when work is completed it is added to a running total. What I would like to do is show the time completed versus the total time spent on the work item. Table 1 is what I have, and Table 2 is what I am trying to show. Any help is much appreciated!
Edit: I do have an Index column as well, but it is not shown here.
Edit2: I calculated Work Completed in Table 2 by subtracting the value in Completed Work by the previous Value in Completed Work. For example, on 2/14 a total of 12 hours of work has been completed on Work Item ID = 123. On 2/15 and addition 66.5 hours of work was completed on 123 for a total of 78.5 hours. I am trying to create a column to show that difference instead of the total hours complete.
Table 1
| Date | Work Item Id | Completed Work |
| 2/12/2018 | 123 | |
| 2/13/2018 | 123 | |
| 2/14/2018 | 123 | 12 |
| 2/15/2018 | 123 | 78.5 |
| 2/16/2018 | 123 | 89.8 |
| 2/17/2018 | 123 | 89.8 |
| 2/18/2018 | 123 | 89.8 |
| 2/19/2018 | 123 | 89.8 |
| 2/20/2018 | 123 | 89.8 |
| 2/21/2018 | 123 | 99.3 |
| 2/22/2018 | 123 | 99.3 |
| 2/23/2018 | 123 | 99.3 |
Table 2 (this table does not exist, I am just using it to show what I am trying to achieve with table 1.)
| Date | Work Item Id | Completed Work | Work Completed |
| 2/12/2018 | 123 | 0 | |
| 2/13/2018 | 123 | 0 | |
| 2/14/2018 | 123 | 12 | 12 |
| 2/15/2018 | 123 | 78.5 | 66.5 |
| 2/16/2018 | 123 | 89.8 | 11.3 |
| 2/17/2018 | 123 | 89.8 | 0 |
| 2/18/2018 | 123 | 89.8 | 0 |
| 2/19/2018 | 123 | 89.8 | 0 |
| 2/20/2018 | 123 | 89.8 | 0 |
| 2/21/2018 | 123 | 99.3 | 9.5 |
| 2/22/2018 | 123 | 99.3 | 0 |
| 2/23/2018 | 123 | 99.3 | 0 |
HI Ski900,
You can use below measure to get diff of previous work date.
Work Completed = VAR _current = MAX ( Test[Date] ) VAR _previous = MAXX ( FILTER ( ALL ( Test ), [Work Item Id] = MAX ( [Work Item Id] ) && [Date] < _current ), [Date] ) RETURN CALCULATE ( SUM ( Test[Completed Work] ), Test[Date] = _current, VALUES ( Test[Work Item Id] ) ) - CALCULATE ( SUM ( Test[Completed Work] ), Test[Date] = _previous, VALUES ( Test[Work Item Id] ) ) + 0Regards,
Xiaoxin Sheng
9 Replies
- Ashish_Mathur
Super User
Hi,
This is the calculated column formula i wrote
=Data[Completed Work]-CALCULATE(MAX(Data[Completed Work]),FILTER(Data,Data[Work Item Id]=EARLIER(Data[Work Item Id])&&Data[Date]<EARLIER(Data[Date])))
Hope this helps.
- chethan
Resolver III
Create the Unique Colour Example: Date & Work Item ID in Both the Table
Create the Relationship with the both & create the Calculated Measure.Measure = SUM ( 'Table1'[DateWork Item ID] ) - SUM ( 'Table2'[DateWork Item ID]
Just Check it will work if not Please Share the sample data with the sample output in Excel
Regards,
Chetan K
- chethan
Try This it will work
Before that Create Index
Portfolio Index Measure = CALCULATE ( 100 * PRODUCTX ( Table1, Table1[Compleated Work] ), FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) ) )
