Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi team, my brain isn't working today so I am hoping you can help out.
As shown below, I have
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!
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 |
Solved! Go to Solution.
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.
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.
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. 🙂
The followowing in Power Query will get you to your original desired output.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |