Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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,
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 ) )
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.
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,
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?
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |