Calculated column based on two other column

[shamim20]

Hello altruists, 

Need some help here.

I have 3 columns of data with millions of rows ( File, record and Time). Each file may have multiple records. Would like to create 4 new columns calculating time difference between two records ( 0 to 1, 1 to 2 and so on ) for each file. Expected outputs are showed in last 4 columns ( Record_0to1 and so on) . Would appreciate any help with this. Thanks

File	Record	Time	Record_0to1	Record_1to2	Record_2to3	Record_3to4
A	0	100	10	15	5	40
A	1	90	10	15	5	40
A	2	75	10	15	5	40
A	3	70	10	15	5	40
A	4	30	10	15	5	40
B	0	200	20	30	10	20
B	1	180	20	30	10	20
B	2	150	20	30	10	20
B	3	140	20	30	10	20
B	4	120	20	30	10	20
C	0	400	50	40	60	50
C	1	350	50	40	60	50
C	2	310	50	40	60	50
C	3	250	50	40	60	50
C	4	200	50	40	60	50

[parry2k · Accepted solution]

@shamim20 add four measures, here is example of 0 - 1

Diff 0 to 1 = 
VAR __filter = ALLEXCEPT ( 'Table', 'Table'[File] )
VAR __valStart = CALCULATE ( [Sum], __filter, 'Table'[Record] = 0 )
VAR __valEnd = CALCULATE ( [Sum], __filter, 'Table'[Record] = 1 )
RETURN
__valStart  - __valStart

[shamim20]

Hi,

Thanks for your reply. What are you referring to as sum here >>

[Sum]

[shamim20]

This is what I ahve at the end

Diff 0 to 1 = 
VAR __filter = ALLEXCEPT ( 'Table', 'Table'[File] )
VAR __valStart = CALCULATE ( SUM(TABLE.Time), __filter, 'Table'[Record] = 0 )
VAR __valEnd = CALCULATE ( [SUM(TABLE.Time),__filter, 'Table'[Record] = 1 )
RETURN
__valStart  - __valStart