OK, please check this to be sure, but I think I have a working solution here. As I mentioned above, the solution is orders of magnitude easier IMO if done in Power Query than in DAX. Most of the time when you’re traversing up and down columns to locate specific values, PQ will be a lot easier than DAX. So here’s the outline of what I did:
The first thing was to conceptually sketch out that we would need a column like the one highlighted in red below that was an indicator of the breakpoints for resetting the cumulative total, based on your decision rules The specific values in this column don’t matter - just the relative position of when they change.
The next steps are all in PQ: (note: this seems quite complicated, but I’m confident that when you work through the logic of what I’ve done via the applied steps in PQ it will make good sense to you. If not, just give a shout -I’'m happy to answer questions.)
- Add an Index column starting at 1 - this is the key to traversing up and down the columns
- Calculate the cumulative total in a Custom column, using the following M code:
Duplicate the index column, add +1 to each value and rename it “Index Add 1”.
Merge the fAmount table with itself, with Index and Index Add 1 as the keys:
Expand the tables created and select the new Cumul Amt and change the name to Prev Cumul Amount.
Create another custom column called Cumul Reset (this is the field that will control the breakpoint resets for the cumulative totals), using the following M code to detect a change in sign:
if [Index] = 1 then 1 else
if (Number.Sign( [Cumul Amt] ) = Number.Sign( [Prev Cumul Amt] )) then null else [Index]
Sort the table by index, and then fill down on the newly created column to replace the nulls with the same breakpoint value, all the way down to the next breakpoint value.
Do the same merge as in 4) above again. This is because your cumulative total resets the record after a sign change is detected.
Do the same expansion and fill down as above in 5), but this time on the newly created Cumul Reset column. You’ll also need to end this with a Fill Up command. Rename column to "Shifted Cumul Reset))
Change all the relevant types to numeric, close and apply, and you should have the table above, missing the final column.
For that column, use this measure:
Cumul Amt Reset =
ALL( Dates ),
Dates[Date] <= MAX( Dates[Date] )
fAmount[Shifted Cumul Reset]
Add that measure to your table, and hopefully you’re good to go:
I hope this is helpful. Full solution file attached.
Very cool problem - I learned a lot working on it.