Find datetime of Max Output (when more than one record can have max output)

Mike_A
Participant V

Hello All. I am creating an exercise tracking app (thanks Covid!). I am trying to figure out how to create a virtual column to contain the latest date/time when my highest output occurred. The data is in a single table and the columns of interest are [Date Of Workout] and [Total Output].

I created a [Max Output] VC that gets me the MAX OUTPUT from the records of interest:
MAX(SELECT(Workouts[Total Output],AND([Fitness Discipline]="Cycling",[Total Output]>0)))

I am now trying to create a VC for [Date of Max Workout], which means I need to find the latest workout record from those where the max output occurred. Soโ€ฆ was hoping to SELECT all the records where [Total Output] = [Max Output], and then select only the latest workout record from that subset.

Can anyone suggest the easiest formula to do this?

0 7 269
7 REPLIES 7

One approach may be to have a two step

  1. Find the row with the max workout with a VC called say [MaxTotalOutput]

MAXROW(โ€œWorkoutsโ€ , โ€œTotal Outputโ€, AND([Fitness Discipline]=โ€œCyclingโ€,[Total Output]>0))

  1. The date then can be found in the VC [Date of Max Workout] with an expression

                       [MaxTotalOutput].[Date Of Workout]

Thanks for the suggestion. Not sure if this solves the case when more than 1 record can have the same max output (eg: last Tue and this Wed both has the same Output)?

Example:
date output
Mar2. 200
Mar5. 180
Mar7. 200

I want to determine the โ€œlatest max output dateโ€ which would be Mar7 in this case.

Thank you. Got it. I believe in that case you may need to use ORDERBY() in place of MAXROW() in that case for the first VC. Something like below

INDEX(ORDERBY( FILTER(โ€œWorkoutsโ€ , AND([Fitness Discipline]=โ€œCyclingโ€,[Total Output]>0)), [Total Output], TRUE, [Date of Workout], TRUE), 1)

Then the second column computation remains same.

Edit: Based the above expression on the example under โ€œRow with maximum valueโ€ in the help article on ORDERBY()

Thanks!! Will take a look later. Didnโ€™t want to get more complicated than needed and had not checked out OrderBy. Appreciated

You are welcome @Mike . Nice to see you posting in the community again after some gap.

I agreed with the approach shown by @Suvrutt_Gurjar
Better to use Orderby expression.

You can make that field to Ref to pull the values from other column from that as well. Getting more flexible to do more.

When it comes to orderby expression, make sure to pass the ID values , otherwise we see unexpected result out of it.

@Suvrutt_Gurjar @tsuji_koichi

Thanks for the insights. And yesโ€ฆ I have been away for quite some time and trying to catch back up to the latest evolutions with my favorite tool! Great to see the community is still active and vibrant.

Will play around a bit with Orderby as that should solve it. Thanks again for the inputs and for taking the time to share knowledge.

Top Labels in this Space