cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
brianlehr Frequent Visitor
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.

 

Thanks in advance for any help you can provide!

 

TimestampGroupRunningTotalMaxTotal From Group and TimePeriod (desired)
2/21/2019 0:00GroupA17
2/21/2019 1:00GroupA27
2/21/2019 2:00GroupA37
2/21/2019 3:00GroupA47
2/21/2019 4:00GroupA57
2/21/2019 5:00GroupA67
2/21/2019 6:00GroupA77
2/23/2019 4:00GroupB12
2/23/2019 5:00GroupB22
2/23/2019 5:00GroupA19
2/23/2019 6:00GroupA29
2/23/2019 7:00GroupA39
2/23/2019 8:00GroupA49
2/23/2019 9:00GroupA59
2/23/2019 10:00GroupA69
2/23/2019 11:00GroupA79
2/23/2019 12:00GroupA89
2/23/2019 13:00GroupA99
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


3 REPLIES 3
Super User
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


brianlehr Frequent Visitor
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

 

TimestampGroupRunningTotalMaxTotal From Group and TimePeriod (desired)
2/21/2019 0:00GroupA17
2/21/2019 1:00GroupA27
2/21/2019 2:00GroupA37
2/21/2019 3:00GroupA47
2/21/2019 4:00GroupA57
2/21/2019 5:00GroupA67
2/21/2019 6:00GroupA77
2/23/2019 4:00GroupB12
2/23/2019 5:00GroupB22
2/23/2019 5:00GroupA119
2/23/2019 6:00GroupA219
2/23/2019 7:00GroupA319
2/23/2019 8:00GroupA419
2/23/2019 9:00GroupA519
2/23/2019 10:00GroupA619
2/23/2019 11:00GroupA719
2/23/2019 12:00GroupA819
2/23/2019 13:00GroupA919
2/23/2019 14:00GroupA1019
2/23/2019 15:00GroupA1119
2/23/2019 16:00GroupA1219
2/23/2019 17:00GroupA1319
2/23/2019 18:00GroupA1419
2/23/2019 19:00GroupA1519
2/23/2019 20:00GroupA1619
2/23/2019 21:00GroupA1719
2/23/2019 22:00GroupA1819
2/23/2019 23:00GroupA1919
2/24/2019 0:00GroupA2025

 

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. :-)

Nick_M New Contributor
New Contributor

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

@brianlehr,

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. Group By.png
  4. Expand the data:
  5. Expand data.png
  6. Then then final table which I believe matches your desired max
  7. Final Table.png