Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rolling Max in calcaluated column

Hi.

I need to make a rolling max in a calculated column, which calculates the maximum value for the last 200 hours, of the Msteam.

I have data for every hour of the year.

My data looks like this.
 

 

Can someone please help. 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may add an index column in query editor first.Then create the measure:

    Measure =
CALCULATE (
    MAX ( Table1[Msteam] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Index]
            > MAX ( Table1[Index] ) - 200
            && Table1[Index] <= MAX ( Table1[Index] )
    )
)

    Regards,

6 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous,

    DateTime type is actually stored as a number. Check this out.

    So you can try this for your calculated column, where Table1 is the table you show:

     

    RollingMax =
VAR _HourStep = 1 / 24
VAR _CutOffTime = Table1[Date/time] - ( _HourStep * 200 )
RETURN
    CALCULATE (
        MAX ( Table1[Msteam] ),
        Table1[Date/time] > _CutOffTime,
        ALL ( Table1 )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi.
      Thank you for your answer.
      However, it does not return the desired values.
      As you can see from the picture, the rolling max value starts at a higher value than the actual steam value at time 1.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi
      Thank you for your answer.

      However, it does not return the desired value.
      As you can see from the attached picture, the rolling max, starts whit a higher value than the msteam.

       

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous 

        You may add an index column in query editor first.Then create the measure:

        Measure =
CALCULATE (
    MAX ( Table1[Msteam] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Index]
            > MAX ( Table1[Index] ) - 200
            && Table1[Index] <= MAX ( Table1[Index] )
    )
)

        Regards,