Creating a last maximum? calculation

[rnehrboss]

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 (84 in this example) so I can have a scorecard that says something like "16% used in 2 Hours and 24 Minutes"

Thoughts?

Thanks!

[ryan_mayu]

@rnehrboss 

maybe you can try this

Measure = 
VAR _localmax=max('Table'[Battery])
VAR _MAX=MAX('Table'[created Time])
VAR _last=maxx(FILTER('Table','Table'[created Time]=_MAX),'Table'[Battery])
VAR _lastmaxtime=maxx(FILTER('Table','Table'[Battery]=_localmax),'Table'[created Time])
VAR _m=DATEDIFF(_lastmaxtime,_MAX,MINUTE)
VAR _hour=roundup((_m/60),0)
VAR _min=mod(_m,60)
return (_localmax-_last)&"% used in "& _hour &" Hours and "&_min&" Minutes"

pls see the attachment below

[rnehrboss]

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%.     

[ryan_mayu]

@rnehrboss 

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

[rnehrboss]

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?

[ryan_mayu]

@rnehrboss 

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

[rnehrboss]

Anyone able to help?

Let me know if I didn't explain it well.

Thanks!

[v-rzhou-msft]

Hi @rnehrboss ,

You can try this code to achieve your goal.

Measure = 
VAR _LastDatetime_100 = CALCULATE(MAX('Table'[create_time]),FILTER('Table','Table'[Battery] = 100))
VAR _LatestDate = CALCULATE(MAX('Table'[create_time]))
VAR _LatestDate_Battery = CALCULATE(MIN('Table'[Battery]),FILTER('Table','Table'[create_time] = _LatestDate))
VAR _Battery_Diff = 100-_LatestDate_Battery
VAR _DateDiff = DATEDIFF(_LastDatetime_100,_LatestDate,MINUTE)
VAR _Hour = QUOTIENT(_DateDiff,60)
VAR _Minutes = MOD(_DateDiff,60)
RETURN
COMBINEVALUES(" ",_Battery_Diff,"% used in",_Hour,"Hours and",_Minutes,"Minutes")

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rnehrboss]

Thanks Rico,

It's a good solution if the battery is taken all the way to 100.   But if not, it won't work.  I'm really looking for the local maximum (meaning the last number where the number before and after are smaller).  That way if charged to 90% it will still find that time and calculate correctly.

Thanks!