Forum Discussion
Row over row logic
A bit stumped here. Trying to enhance a dataset with a column that uses some logic. The data is coming from general ledger entries with the the two (or more) accounts an document posts to. Wh...
Close!
Unfortunately, I messed up my sample data. The real data doesn't always have the negative values last, hence the need to sum until we get a zero total.
Updated sample data:
| Doc | Line | Amount |
| 1 | 1 | 10 |
| 1 | 2 | -10 |
| 2 | 1 | -20 |
| 2 | 2 | 20 |
| 3 | 1 | 10 |
| 3 | 2 | 10 |
| 3 | 3 | -20 |
| 4 | 1 | 30 |
| 4 | 2 | -30 |
| 4 | 3 | 30 |
| 4 | 4 | -40 |
| 4 | 5 | 10 |
Can you include where you expect the flags in this updated scenario?
Whichever result is easiest to produce.
I could use any of these result columns:
Doc Line Amount Action# Flag(start) Flag(end) 1 1 10 1 X 1 2 -10 1 x 2 1 -20 2 x 2 2 20 2 x 3 1 10 3 x 3 2 10 3 3 3 -20 3 x 4 1 30 4 x 4 2 -30 4 x 4 3 30 5 x 4 4 -40 5 4 5 10 5 x Got it. I think this works, but I created this as a measure now instead of a calculated column like the previous approach. I set this up to represent your "Flag(end)." This could be easily modified for the other scenario if that's desired, though. Here's the new calc:
Flag Final = IF(SUMX(FILTER(ALL(Sheet1),Sheet1[Index]<= SELECTEDVALUE(Sheet1[Index])),Sheet1[Amount]) = 0,1)And the output:Let me know if this works.BenAwesome!
I modified to a calculated column and switched to flagging the start.
Flag = IF(SUMX(FILTER(ALL(Table1),Table1[Index]< EARLIER(Table1[Index])),[Amount]) = 0,1)Thanks again!