Checking if a deleted value was added onto another line

[PowerBI123456]

Hi, would appreciate some help. I am trying to figure out how to see if a charge that was deleted was added to another charge. For example, Charge 1 is 5, and then it was deleted. It was then added onto charge 2, you can tell since charge 2 was originally 10 but it was updated to 15. Same thing with Charge 3, its originally 3, then deleted. You can see that Charge 6 went from 9 to 12 (difference of 3). Hope that makes sense. I am dealing with 100,000s of rows, so needs to be optimal. Thanks!

[az38]

hi @PowerBI123456 

so, whats your desired output? how should look like result?

[PowerBI123456]

@az38  Hi, trying to see if a deleted charge was added to another charge. I updated below, so charge 7 was deleted, but was not added to another charge. Hope that makes sense.

[PowerBI123456]

@az38  Forgot to mention there are certain ID to factor in. So for example, ID 2 has a charge that was updated from 5 to 7 (difference of 2), but that can't be from charge 7 being deleted from ID 1. 

[Icey]

Hi @PowerBI123456 ,

Could the method @az38 posted meet your requirements?

If it could, please accept his reply as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

In addition, is it necessary to judge whether the difference is the same as the deleted line value? What if they are not the same?

Best Regards,

Icey

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

[PowerBI123456]

@Icey yes, the difference needs to be exactly the same. 

[PowerBI123456]

@az38  thank you so much! It looks like it worked. 

[PowerBI123456]

@az38  Could you please help me understand why you wrote the variable out the way you did? Just trying to understand it better., specifically the ">Earlier" part.  Thanks!!!

[az38]

@PowerBI123456 

EARLIER() in calculated column refers to the currrent row value. so, we need to define if there is exists a row after current with given conditions

[PowerBI123456]

@az38thank you!!!

I am testing it out now, and also need to verify against a system. I will let you know once I am done, thanks again!!! 

[az38 · Accepted solution]

@PowerBI123456 

if I understand you correct try a column

Added = 
var _isUpdatedAfter = CALCULATE(COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[ID]), 'Table'[Step] = "Updated" && 'Table'[Charges] > EARLIER('Table'[Charges])))
RETURN
IF([Step] = "Line deleted" && _isUpdatedAfter > 0, "yes", "no")