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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Highlighted
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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

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. 🙂

Super User
Super User

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 57 members 982 guests
Please welcome our newest community members: