Forum Discussion
Calculating difference between rows based on multiple columns
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.
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.
4 Replies
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.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.
