Forum Discussion
Filter Max hour by Date
Hello everybody.
I want to filter the table below to show me the column "Production" for the maximum hour of every day. How can I do this?
| Date | Hour | Production |
| 01/01/2022 | 05:00:00 | 10 |
| 01/01/2022 | 06:00:00 | 20 |
| 01/01/2022 | 07:00:00 | 30 |
| 01/01/2022 | 08:00:00 | 40 |
| 01/01/2022 | 09:00:00 | 50 |
| 01/01/2022 | 10:00:00 | 60 |
| 01/01/2022 | 11:00:00 | 70 |
| 01/01/2022 | 12:00:00 | 80 |
| 01/01/2022 | 13:00:00 | 90 |
| 01/01/2022 | 14:00:00 | 100 |
| 01/01/2022 | 15:00:00 | 110 |
| 01/01/2022 | 16:00:00 | 120 |
| 01/01/2022 | 17:00:00 | 130 |
| 01/01/2022 | 18:00:00 | 140 |
| 01/01/2022 | 19:00:00 | 150 |
| 01/01/2022 | 20:00:00 | 160 |
| 01/01/2022 | 21:00:00 | 170 |
| 01/01/2022 | 22:00:00 | 180 |
| 01/01/2022 | 23:00:00 | 190 |
| 02/01/2022 | 05:02:03 | 9 |
| 02/01/2022 | 06:01:59 | 18 |
| 02/01/2022 | 07:01:55 | 27 |
| 02/01/2022 | 08:01:51 | 36 |
| 02/01/2022 | 09:01:47 | 45 |
| 02/01/2022 | 10:01:43 | 54 |
| 02/01/2022 | 11:01:39 | 63 |
| 02/01/2022 | 12:01:35 | 72 |
| 02/01/2022 | 13:01:31 | 81 |
| 02/01/2022 | 14:01:27 | 90 |
| 02/01/2022 | 15:01:23 | 99 |
| 02/01/2022 | 16:01:19 | 108 |
| 02/01/2022 | 17:01:15 | 117 |
| 02/01/2022 | 18:01:11 | 126 |
| 02/01/2022 | 19:01:07 | 135 |
| 02/01/2022 | 20:01:03 | 144 |
| 02/01/2022 | 21:00:59 | 153 |
| 02/01/2022 | 22:00:55 | 162 |
HI GustavoKamchen,
You can create a calculated column with the following formula to use the current date to find 'hour', then use the above 'hour' and current date to get the corresponding Production values.
formula = VAR _maxhour = CALCULATE ( MAX ( Table[Hour] ), FILTER ( Table, [Date] = EARLIER ( Table[Date] ) ) ) RETURN CALCULATE ( MAX ( Table[Production] ), FILTER ( Table, [Date] = EARLIER ( Table[Date] ) && [Hour] = _maxhour ) )EARLIER, EARLIEST – DAX Guide - SQLBI
Regards,
Xiaoxin Sheng
5 Replies
HI GustavoKamchen,
You can create a calculated column with the following formula to use the current date to find 'hour', then use the above 'hour' and current date to get the corresponding Production values.
formula = VAR _maxhour = CALCULATE ( MAX ( Table[Hour] ), FILTER ( Table, [Date] = EARLIER ( Table[Date] ) ) ) RETURN CALCULATE ( MAX ( Table[Production] ), FILTER ( Table, [Date] = EARLIER ( Table[Date] ) && [Hour] = _maxhour ) )EARLIER, EARLIEST – DAX Guide - SQLBI
Regards,
Xiaoxin Sheng
Hello,
I wrote there my question. https://community.powerbi.com/t5/Desktop/Show-only-data-from-the-latest-date/m-p/2852862/highlight/true#M983300
So i saw yor codes and i have tried but nothing happened. There was no error.
Can you help me?
note: I don't want to see earlier dates, i need last date and hour line.
Thak you
How can I calculate average of daily production?
- Ashish_Mathur
Super User
PVO3Impactful Individual
If you would like to create a table:
Table =SUMMARIZE(Data,Data[Date],"Hour",MAX(Data[Hour]),"Production",CALCULATE(MIN(Data[Production]), SELECTEDVALUE(Data[Date])=Data[Date], MAX(Data[Hour]) = Data[Hour]))Result:
You might want to format or change the hour column according to your needs.
