Forum Discussion

alfredorhz's avatar
alfredorhz
Helper I
4 years ago
Solved

rolling 3 values sum

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


Operation = value1/value2

Thanks a lot

 

 

 

Need Help
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    4 years ago

    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] )
        )
    )

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      alfredorhz
      Helper I
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        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?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
      alfredorhz
      Helper I
      4 years ago

      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's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

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