rolling 3 values sum

[alfredorhz]

Hi Cummunity
Could please help me with rolling 3 values whitout dates, for example

Operation = value1/value2

Thanks a lot

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(countrows(Data),FILTER(Data,Data[Index]>=EARLIER(Data[Index])-2&&Data[Index]<=EARLIER(Data[Index])))<=2,BLANK(),divide(CALCULATE(SUM(Data[value]),FILTER(Data,Data[Index]>=EARLIER(Data[Index])-2&&Data[Index]<=EARLIER(Data[Index]))),CALCULATE(SUM(Data[value2]),FILTER(Data,Data[Index]>=EARLIER(Data[Index])-2&&Data[Index]<=EARLIER(Data[Index])))))

Hope this helps.

[alfredorhz]

Thank yoy @Ashish_Mathur
I don´t know why the index values in the EARLY function send this error, 'parameter is not the correct type'. any clue?

 

[Ashish_Mathur]

Hi,

In the Query Editor, ensure that the Data type of the Index column is whole number.

[tamerj1 · Accepted solution]

Hi @alfredorhz 
Please refer to attached file

3rolling = 
VAR CurrentIndex = Data[Index]
VAR OnAndBeforeTable = FILTER ( Data, Data[Index] <= CurrentIndex )
VAR Last3Table = TOPN ( 3, OnAndBeforeTable, [Index] )
RETURN
    IF ( 
        COUNTROWS ( Last3Table ) = 3,
        DIVIDE ( 
            SUMX ( Last3Table, [value1] ),
            SUMX ( Last3Table, [value2] )
        )
    )

[alfredorhz]

HI @tamerj1 
Thanks for your support
May be I forgot write somenthing, Colum2 values is a calculate column, and I try to use the SELECTVALUE function but didn´t works, send me "the expresion refers to multiple colums" I don´t know if was usin the wrong funcion....

[tamerj1]

Hi @alfredorhz 

I just noticed your reply. Why do you need to use SELECTEDVALUE? It is irrelevant that Value2 is a calculated column, the code I proposed should work either ways. Did you try it? If so, what results did you get?