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 th...
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,

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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi. 
    Thank you for the answer. 

    It solved the problem per say, but, i encountered another problem.

    I lack the computational power to solve 100,000 rows.

    I ran the solution on 2,000  rows with no problems, but with 100,000 i lack memory (i have 15 gb memory, and only Power Bi running)

    Could the calcautions be done in an other way? be done in another way?

    • Power_BI_Help's avatar
      Power_BI_Help
      Icon for Helper I rankHelper I
      6 years ago

      I removed the - 200 rows portion. Some reason my column isn't actually filtering it is grabbing the highest value in the column and populating through the entire column. Ideas? 

      Portfolio - Equity Total EOD ($) Peak Value =
      CALCULATE(
      MAX('Daily Ledgers'[Portfolio - Equity Total EOD ($)]),
      FILTER(
      ALL('Daily Ledgers'),
      'Daily Ledgers'[Index] <= MAX('Daily Ledgers'[Index])
      )
      )