Forum Discussion

rnehrboss's avatar
rnehrboss
Icon for Helper II rankHelper II
4 years ago

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 ...
Need Help
Tutorial Requests
rnehrboss's avatar
rnehrboss
Icon for Helper II rankHelper II
4 years ago

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's avatar
ryan_mayu
Icon for Super User rankSuper User
4 years ago

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's avatar
    rnehrboss
    Icon for Helper II rankHelper II
    4 years ago

    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's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      4 years ago

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