Subtract values from the previest row

[edubcardoso]

Hey!

I need to subtract initial KMS from the final KMS in previous value,  , to check errors

day	user	initial KMS	final kms	Diff	measure i wanna calculate
10-06-2022	1	100	126	26
10-06-2022	2	256	286	30
13-06-2022	1	130	180	50	130 - 126 = 4
14-06-2022	1	180	200	20	0
15-06-2022	1	205	250	45	205 - 200 = 5

I cant use index ranking from the power query because a have a lot of users.

Can you help me, please ?

Sorry for my bad english

Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @edubcardoso ,

Here are the steps you can follow：

1. Create calculated column.

Rank_Column = RANKX(FILTER(ALL('Table'),'Table'[user]=EARLIER('Table'[user])),'Table'[day],,ASC)

Mod_column = MOD('Table'[Rank_Column],2)

2. Create measure.

Measure1 =
var _1=CALCULATE(SUM('Table'[initial KMS]),FILTER(ALL('Table'),'Table'[user]=MAX('Table'[user])&&'Table'[Mod_column]=MAX('Table'[Mod_column])&&'Table'[Rank_Column]=MAX('Table'[Rank_Column])))
return
IF(
    MAX('Table'[Mod_column])=1,0,_1
)

Measure2 =
CALCULATE(SUM('Table'[final kms]),FILTER(ALL('Table'),'Table'[user]=MAX('Table'[user])&&'Table'[Mod_column]=MAX('Table'[Mod_column])+1&&'Table'[Rank_Column]=MAX('Table'[Rank_Column])-1))

measure calculate =
[Measure1] - [Measure2]

3. Result:

Best Regards,

Liu Yang

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

[SpartaBI]

@edubcardoso you need a measure or a calculated column?
The table you sent is a table visual or this is how your data looks like in the model table:

[edubcardoso]

@SpartaBI 
A measure.

Thanks for the help!

[SpartaBI]

@edubcardoso my pleasure. 
Can you share a picture of how your data table look like.
Also, in the table you showed, which one is a column there and which one is a measure? And if you have measures there, what are they? How do you calculate currently initial KMS and final kms ?

[edubcardoso]

The table the I have looks like the pic you showed, only missing 2 ou 3 column like the name of the user, but that columns are not needed in this measure.

The only measure in the table is the last column . Its the one i wanna calculate.

The KMs (final and initial) are filled in by the user in a software

The main goal of the measure its to see where the user made a mistake

[SpartaBI]

@edubcardoso will pm you

[SpartaBI]

@edubcardoso I sent you a private message