Forum Discussion
Creating a last maximum? calculation
I'd like to create a measure that looks through last records finds the local max (100 in this example) and then uses the time and battery level from that record, along with the most recent local min ...
Thanks Ryan, Great solution and will kind of work if the battery is always charged to 100%. If the last charge was unnplugged at 90% (which would be the local max), I think the _lastmaxtime var will go all the way back to the last 100%.
maybe you can create a column first
status =
VAR _last=maxx(FILTER('Table','Table'[created Time]<EARLIER('Table'[created Time])),'Table'[created Time])
var _lastvalue= maxx(FILTER('Table','Table'[created Time]=_last),'Table'[Battery])
return if(_lastvalue>'Table'[Battery],"charging","discharging")then create a measure
Measure =
VAR _localmaxtime=maxx(FILTER('Table','Table'[status]="discharging"),'Table'[created Time])
VAR _localmax=maxx(FILTER('Table','Table'[created Time]=_localmaxtime),'Table'[Battery])
VAR _MAX=MAX('Table'[created Time])
VAR _last=maxx(FILTER('Table','Table'[created Time]=_MAX),'Table'[Battery])
VAR _m=DATEDIFF(_localmaxtime,_MAX,MINUTE)
VAR _hour=ROUNDDOWN((_m/60),0)
VAR _min=mod(_m,60)
return (_localmax-_last)&"% used in "& _hour &" Hours and "&_min&" Minutes"pls see the attachment below
Thanks Ryan!
I think you are close to the right solution. One thing I didn't include (sorry) is that I have a column with devices. So I think the "status" column is calculating based on all reported device percentages instead of grouping and calculating. Is it possible to have the "status" column calculate on groupings?
pls try this
status = VAR _last = maxx(FILTER('Table','Table'[create_time]<EARLIER('Table'[create_time])&&'Table'[DeviceID]=EARLIER('Table'[DeviceID])),'Table'[create_time]) var _lastvalue= maxx(FILTER('Table','Table'[create_time]=_last),'Table'[Battery]) return if(_lastvalue<'Table'[Battery],"charging","discharging")