Creating a column (or measure) based on the difference in values from another column

[Ski900]

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

[Ashish_Mathur]

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]

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] ) )
)

[chethan]

@Ski900

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

[Ski900]

My apologies for the confusion. Table 2 does not exist, I just included it to show what I am trying to achieve with Table 1. Hopefully this clarifies the issue. If not please let me know.

[chethan]

How did you derived  Work Compleated on what bases this coloum calculated...

[Ski900]

Completed Work is pulled from VSO and is entered in by developers as the work on tasks.

[Anonymous · Accepted solution]

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] )
        )
        + 0

Regards,

Xiaoxin Sheng

[Ski900]

Works perfectly, thanks!

[Ski900]

I updated the post to include a new edit. Hopefully this helps, thanks!