Row over row logic

Hi,

I think there are two ways to accomplish this, assuming your data structure looks like what you've provided. To me, this feels like this flag should be at the grain of the data, so it could be included as a calculated column in your data source. So, what I've done is sorted the data by Doc, and then Line, and then just created an Index from within the Power Query Editor, with the "starting at 1" option:

From there, I created a calculated column with the following logic:

Flag1 =

IF(

OR(

LOOKUPVALUE(Sheet1[Amount],Sheet1[Index],Sheet1[Index]-1) < 0,

Sheet1[Index] = 1

),

1

)

 

That gives me the following output:

 

Let me know if this works for you. I also got this output with a calculated measure instead. Let me know if you'd like to see this option as well.

 

Thanks,

Ben