Forum Discussion
Advocate ISubtract values from the previest row
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!
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
7 Replies
- SpartaBI
Community Champion
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:
edubcardosoSpartaBI
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 ?
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
