Rolling Max in calcaluated column

[Anonymous]

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. 

[AlB]

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]

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 · Accepted solution]

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]

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]

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

)

)

[Anonymous]

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.