cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Max value of number from group over given time period

Hi team, my brain isn't working today so I am hoping you can help out.

As shown below, I have

• Hourly timestamp
• Running total which corresponds per hour a certain Group is primary
• Groups

What I'm trying to find is the maximum value (from the RunningTotal field) over a given timeperiod (as shown by Timestamp) for a given Group.  Note that there is a different Max for GroupA depending on the time period.  I've tried using something like what is here but that just gets me 9 for all of GroupA regardless of time window.

 Timestamp Group RunningTotal MaxTotal From Group and TimePeriod (desired) 2/21/2019 0:00 GroupA 1 7 2/21/2019 1:00 GroupA 2 7 2/21/2019 2:00 GroupA 3 7 2/21/2019 3:00 GroupA 4 7 2/21/2019 4:00 GroupA 5 7 2/21/2019 5:00 GroupA 6 7 2/21/2019 6:00 GroupA 7 7 2/23/2019 4:00 GroupB 1 2 2/23/2019 5:00 GroupB 2 2 2/23/2019 5:00 GroupA 1 9 2/23/2019 6:00 GroupA 2 9 2/23/2019 7:00 GroupA 3 9 2/23/2019 8:00 GroupA 4 9 2/23/2019 9:00 GroupA 5 9 2/23/2019 10:00 GroupA 6 9 2/23/2019 11:00 GroupA 7 9 2/23/2019 12:00 GroupA 8 9 2/23/2019 13:00 GroupA 9 9
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Max value of number from group over given time period

As a column, you could do this:

Column = MAXX(FILTER(ALL('Table1'),[Group]=EARLIER([Group]) && DATE(YEAR([Timestamp]),MONTH([Timestamp]),DAY([Timestamp]))=DATE(YEAR(EARLIER([Timestamp])),MONTH(EARLIER(Table1[Timestamp])),DAY(EARLIER([Timestamp])))),[RunningTotal])

Attached, Table1.

Proud to be a Datanaut!

3 REPLIES 3
Super User

Re: Max value of number from group over given time period

As a column, you could do this:

Column = MAXX(FILTER(ALL('Table1'),[Group]=EARLIER([Group]) && DATE(YEAR([Timestamp]),MONTH([Timestamp]),DAY([Timestamp]))=DATE(YEAR(EARLIER([Timestamp])),MONTH(EARLIER(Table1[Timestamp])),DAY(EARLIER([Timestamp])))),[RunningTotal])

Attached, Table1.

Proud to be a Datanaut!

Frequent Visitor

Re: Max value of number from group over given time period

Thanks Greg!

Interestingly, your methodology "resets" to the next maximum every day (assuming the running count is still going) as opposed to what I was originally envisioning, i.e. that it would just have the highest max applied for all rows in that timeframe.  In other words, when the timestamp gets to x/x/2019 12:00 AM, the RunningTotal keeps going and the new column now jumps to the max for that day.  Eventually on the final day, the max now matches the final max.

Example is shown below

 Timestamp Group RunningTotal MaxTotal From Group and TimePeriod (desired) 2/21/2019 0:00 GroupA 1 7 2/21/2019 1:00 GroupA 2 7 2/21/2019 2:00 GroupA 3 7 2/21/2019 3:00 GroupA 4 7 2/21/2019 4:00 GroupA 5 7 2/21/2019 5:00 GroupA 6 7 2/21/2019 6:00 GroupA 7 7 2/23/2019 4:00 GroupB 1 2 2/23/2019 5:00 GroupB 2 2 2/23/2019 5:00 GroupA 1 19 2/23/2019 6:00 GroupA 2 19 2/23/2019 7:00 GroupA 3 19 2/23/2019 8:00 GroupA 4 19 2/23/2019 9:00 GroupA 5 19 2/23/2019 10:00 GroupA 6 19 2/23/2019 11:00 GroupA 7 19 2/23/2019 12:00 GroupA 8 19 2/23/2019 13:00 GroupA 9 19 2/23/2019 14:00 GroupA 10 19 2/23/2019 15:00 GroupA 11 19 2/23/2019 16:00 GroupA 12 19 2/23/2019 17:00 GroupA 13 19 2/23/2019 18:00 GroupA 14 19 2/23/2019 19:00 GroupA 15 19 2/23/2019 20:00 GroupA 16 19 2/23/2019 21:00 GroupA 17 19 2/23/2019 22:00 GroupA 18 19 2/23/2019 23:00 GroupA 19 19 2/24/2019 0:00 GroupA 20 25

The final number (25) is assuming the 2/24 GroupA continues for 5 more rows before GroupB starts again.  What I had assumed is that it woudl be 25 for all this "set".  So this is a bit different than I had intended but will mark as solution. :-)

New Contributor

Re: Max value of number from group over given time period

The followowing in Power Query will get you to your original desired output.

1. It's better to have separate date and time columns ( vs having it all in one field) has to do with cardinality and helps to have those values in their own columns
2. Group by the following:
3. Expand the data:
4. Then then final table which I believe matches your desired max