Forum Discussion

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

 

Thoughts?

 

Thanks!

 

Need Help
Tutorial Requests

8 Replies

  • rnehrboss's avatar
    rnehrboss
    Helper II
    4 years ago

    Anyone able to help?

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

    Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

        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!

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    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's avatar
      rnehrboss
      Helper 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
        Super 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