Calculating value differences for last 7 days for each element of the columns

[Anonymous]

Hi everyone!

I wanted to ask for a help with a right way of constructing a loop inside the Power Query function.

I have a table of date, index and multiple value columns like below with thousands of rows:
Date                          Index                                    Value1                                   Value2

01/01/2021               1                                           15,2                                        30,8

02/01/2021               2                                           10,8                                        37,9

03/01/2021               3                                           10,4                                        30,6

04/01/2021               4                                           11,0                                        38,4

05/01/2021               5                                           11,5                                        35,8
06/01/2021               6                                           10,5                                        45,8
07/01/2021               7                                           13,7                                        36,3
08/01/2021               8                                           11,5                                        35,8

What I need to achieve is the way of telling if the values in the value columns have not changed for +/- 5 for 7 consecutive days. For example, in the table above that would be Value1 from 2-8 January, and not Value2 column. I need to somehow indicate that on the January 8th I met the criteria.

So far I know that I need to compare previous 6 days' differences of Value1 column for each element of the column. Meaning that for January 8, I need to compare if each difference for the last 7 days is between +5 and -5. My main problem is that I do not know how to access elements of the previous rows through calculated indices (ex. Value1[index-1]).

Does anyone have any idea if this can be achieved through List.Generate or List.Accumulate or in any other way in PowerBI?

[Smauro · Accepted solution]

Hi @Anonymous 

You could try adding this using the Advanced Editor:

    Data = Table.Buffer(Table.AddKey(LastStep, {"Index"}, true)),

    AddLast = Table.AddColumn(

        Data, "Previous Day Diff",

        each let i = [Index] - 1 in Number.Abs( Data{[Index = i]}?[Value1]? - [Value1] )??0,

        type number ),

    AddLast7Check = Table.AddColumn(

        AddLast, "Last 7<5",

        each List.Max(List.Transform({[Index]-6..[Index]-1},

           (n) => AddLast{[Index = n]}?[Previous Day Diff]?) & {[Previous Day Diff]}) < 5,

        type logical)

in

    AddLast7Check

Replacing LastStep with your last step's name.

Cheers

[Anonymous]

@Smauro thank you very much, it worked!