Calculating difference between rows based on multiple columns

[Jimcint]

I have a large table that contains mass measurements from multiple tires at multiple mileages, as shown below:

I would like to create two new columns (MassLossKG and MileDiff) that contain the difference in mass and miles from consecutive measurements of the same tire (same TyreSpec and TyreNum), So, I'm looking for somethine like this:

Miles	TyreSpec	TyreNum	TyreWheelMassKG	MassLossKG	MileDiff
8000	C	3	21.25	.04	800
0	C	4	22.38	null	null
800	C	4	22.36	.02	800
1600	C	4	22.34	.02	800
2400	C	4	22.28	.06	800
3200	C	4	22.24	.04	800
4000	C	4	22.18	.06	800
4800	C	4	22.14	.04	800
5600	C	4	22.11	.03	800
6400	C	4	22.1	.01	800
7200	C	4	22.03	.07	800
8000	C	4	22.01	.02	800
0	D	1	23.12	null	null

Is it possible to do something like this in DAX? How?

If it's not possible to use null or blank values for the 0 mile cases, then 0 is okay. 

[PijushRoy]

Hi @Jimcint 

Your requirements and examples are not clear to me. Can you add more details?
In MassLossKG column 1st value 0.04, what is the calculation?

[Jimcint]

For the fourth row of the table, tire C 4 is at 1600 miles. the mass is 22.34 kg. The previous measurement is 800 miles with a mass of 22.36 kg. so mass loss is 22.36 - 22.34 = 0.02 measurement distance is 1600 - 800 = 800. 

for the first (0 mile) measurement, there is nothing to subtract from, so the results would be null, 0, or blank. 

[Anonymous · Accepted solution]

Hi,

@PijushRoy , thanks for the concern about the question, and i want to offer some more infotmation for user to refer to.

hello @Jimcint , based on your description, you can refer to the following caucluated columns.

MassLossKG =
VAR a =
    CALCULATE (
        SUM ( 'Table'[TyreWheelMassKG] ),
        ALLEXCEPT ( 'Table', 'Table'[TyreSpec], 'Table'[TyreNum] ),
        'Table'[StopId]
            = EARLIER ( 'Table'[StopId] ) - 1
    )
RETURN
    IF ( [Miles] <> 0, ABS ( [TyreWheelMassKG] - a ) )

MileDiff =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Miles] ),
        ALLEXCEPT ( 'Table', 'Table'[TyreSpec], 'Table'[TyreNum] ),
        'Table'[StopId]
            = EARLIER ( 'Table'[StopId] ) - 1
    )
RETURN
    IF ( [Miles] <> 0, ABS ( [Miles] - a ) )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimcint]

Thank you. This is very helpful.