Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brianlehr
Employee
Employee

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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.