Reset next row based on compounded total

[AndyC]

Hello,

I've got most of the way with this but now I'm stuck.

Throughout, I've been using List.Generate within power query  and have linked a sample file below.

Scenario - For each row of gains, there is a signal which initiates a position.

A signal of 1 initiates a position of 1.

A further signal of 1 adds to the position. There is a maximum position of 2 allowed.

A signal of 0 means no change to position.

A signal of -1 takes current position to 0.

Return is then calculated from gain x position.

The returns are then grouped whilst there is a position and a compounded return for each group calculated.

This is as far as I've got.

What I'd like to achieve next is to reset any position to 0 when its compounded return reaches -1% or less.

This would also reset the grouping and thus the compounded return

So, in effect, any initiated positions would be reset to 0 either with a signal of -1 or a compounded return <= -1% and would not initiate again until the next signal of 1.

For example, in the linked file, on the 24/08/2023 the group comp return was -2.49%. On the 25/08/2023 I want the position to reset to 0 along with the grouping and group comp return. The position would then stay at 0 until the next positive signal on the 31/08/2023.

This is the first time I’ve used List.Generate and custom functions and I’m not sure whether I’m going down the correct route. It may be that DAX would provide a better way to do this but I have very little experience of using DAX so went with power query instead.

If anyone could help with this it would be much appreciated.

Sample file 

Andy

[dufoq3]

Hi @AndyC, could you add into your sample file also expected output please?

[AndyC]

Hello @dufoq3 ,

I have now added a table for expected output.

I have highlighted the cells that are different compared to the original table.

Hope this makes sense.

[dufoq3]

Hi, I've create same as you but in a simple query (file is attached).

You can find 2 new versions (so you can delete all the others).
Check [group comp return] column please, because in my opinion you want my v2, but let me know please.

My question is:

why ther is 0 here when signal is 1?

[AndyC]

Hello @dufoq3 

In your v1 the returns are being compounded which is correct.

In your v2 they are being summed.

Although the results are similar in a small group of values I do need the returns to be compounded as in v1.

I would like the position (current row) to be conditional on BOTH the signal (current row) AND the group comp return (GCR) (previous row).

So, even though the signal is 1 in row 20, because the GCR (row 19) had reached -2.92% (i.e. <= -1%) the position in row 20 should reset to 0.

Return (row 20) would then be 0% (0.70%*0) , grouping (row 20) would calcuate to 0 and the GCR (row20) would thus reset to 0%.

The position would then stay at 0 until the next signal of 1 in row 24 and the returns then compounded until either a signal of -1 or GCR <= -1%.

I basically got stuck because the calculated return, grouping and GCR (row 19) are derived from the  position (row 19) and I couldn't figure out how to use the GCR calculated in that previous row (19) in the calculation of the new position in the current row (20).

[dufoq3 · Accepted solution]

Done (I didn't create [grouping] column because it was not necessary for this purpose).

[AndyC]

Thanks very much @dufoq3 !

Would you mind  explaining the code for GeneratedPositionReturnGCR2 so I can fully understand what you've done?

[dufoq3]

Hi @AndyC, you're welcome. I've added some comments:

Whole query with comments for GeneratedPositionReturnGCR2:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Gain", Percentage.Type}, {"signal", Int64.Type}}),
    GeneratedPositionReturnGCR = [ tbl = Table.Buffer(ChangedType[[Gain], [signal]]),
    lg = List.Generate(
            ()=> [ x = 0, p = tbl{x}[signal], r = tbl{x}[Gain] * p, g = r ],
            each [x] < Table.RowCount(tbl),
            each [ x = [x]+1, 
                   p = if tbl{x}[signal] = -1 then 0 else if [p] = 2 then 2 else [p] + tbl{x}[signal],
                   r = tbl{x}[Gain] * p,
                   g = if p = 0 then 0 else  ([g]+1) * (r+1) -1 ],
            each [ position = [p], return = [r], group comp return = [g] ]
    ),
    toTable = Table.FromRecords(lg, type table[position=Int64.Type, return=Percentage.Type, group comp return=Percentage.Type])
  ][toTable],
    Combined = Table.FromColumns(Table.ToColumns(ChangedType) & Table.ToColumns(GeneratedPositionReturnGCR), Value.Type(ChangedType & GeneratedPositionReturnGCR)),
    GeneratedPositionReturnGCR2 = 
    [ tbl = Table.Buffer(Table.SelectColumns(Combined, {"Gain", "signal", "position", "return", "group comp return"})), //Buffered selected columns
      lg = List.Generate(
            ()=> [ x = 0, s = tbl{x}[signal], g = tbl{x}[Gain], gcr = tbl{x}[group comp return] , p = tbl{0}[position], r = tbl{0}[return] ], //Generated 1st row (captured values from previous step 1st row)
            each [x] < Table.RowCount(tbl), //condition when should be generating stopped
            each [ x = [x]+1, //previous row number +1
                   s = tbl{x}[signal], //current row signal
                   p = if [gcr] = 0 and s <> 1 then 0 else if [gcr] = 0 then [p]+1 else if [gcr] > -0.01 then tbl{x}[position] else 0, //if previous row gcr (group comp return) = 0 and current row signal <> 1 then 0 else if previous row gcr = 0 then previous row p (position) + 1 else if previous row gcr > -1% then current row p else 0
                   g = tbl{x}[Gain], //current row Gain
                   r = g * p, //r (row) = current row g (Gain) * current row p (position)
                   gcr = if p = 0 then 0 else ([gcr]+1) * (r+1) -1 ], //if current row p (position) = 0 then 0 else (previous row gcr + 1) * (current row return + 1) - 1
            each [ gain = [g], signal = [s], position = [p], return = [r], group comp return = [gcr] ] //renamed parts of record s to signal, p to position etc...
      ),
      toTable = Table.FromRecords(lg, type table[/* gain=Percentage.Type, signal=Int16.Type, */ position=Int64.Type, return=Percentage.Type, group comp return=Percentage.Type]) //created table from records
    ][toTable],
    Combined2 = [ a = Table.RemoveColumns(Combined, Table.ColumnNames(GeneratedPositionReturnGCR2)),
    b = Table.FromColumns(Table.ToColumns(a) & Table.ToColumns(GeneratedPositionReturnGCR2), Value.Type(a & GeneratedPositionReturnGCR2))
  ][b]
in
    Combined2

[AndyC]

Brilliant, thanks again.

[dufoq3]

Enjoy 😉