Group by day and hour, and get max value from another column

[hekutoru]

 

Hi, I  need to group or get a measure with the datetime and time columns from next sample.

id	datetime	time
1	28/03/2023 09:30:03 pm	205
2	28/03/2023 09:28:50 pm
3	28/03/2023 09:25:10 pm	198
4	28/03/2023 09:20:34 pm
5	28/03/2023 09:11:40 pm	123
6	28/03/2023 09:04:50 pm
7	28/03/2023 08:46:23 pm	112
8	28/03/2023 08:33:55 pm	95
9	28/03/2023 08:18:36 pm
10	28/03/2023 07:48:48 pm
11	28/03/2023 07:28:18 pm	90

The idea is that I need to group by day and hour in 'datetime' column, and get the max value from the 'time' colum, for example for day 28/03 at 9-10pm, i'd need to get the 205, then for 28/03 8-9pm get the 112 value and so. This would also apply when I found the 27/03 day. The idea is to find the max value within the range of 1 hour each hour in a day.

The objective is to get something like this:

To be abel to create a bar chart, each bar representing 1 hour, where I can see what is that maximum value in that hour.

I'd really aprecciate all your support.

[theov]

For peak calculations, have a look in this video as well, very good approach using calculated tables:

https://youtu.be/5eu3b9Q7l9I?si=bICIumapOe6j-nZn

[Anonymous]

You can add two custom columns, DateTime.Date and Time.Hour, then group by date and hour, and use the Max aggregation for your "time" column.

--Nate 

[Greg_Deckler]

@hekutoru Didn't post the sample data as text so having to wing it. Maybe:

Measure =
  VAR __dateTime = MAX('Table'[datetime])
  VAR __date = DATE(__dateTime)
  VAR __hour = HOUR(__dateTime)
  VAR __table = FILTER(ALL('Table'), DATE('Table'[datetime]) = __date && HOUR('Table'[dateTime]) = __hour)
  VAR __result = MAXX(__table, [time])
RETURN
  __result

[hekutoru]

@Greg_Deckler 

thank you, I've already post as text, add some information too

[Greg_Deckler · Accepted solution]

@hekutoru Must have gotten here when it was still an image. See attached PBIX below signature. You create a disconnected table like this:

Dates and Hours = 
    SELECTCOLUMNS(
        ADDCOLUMNS( 
            GENERATE( 
                CALENDAR(MIN('Table'[datetime]), MAX('Table'[datetime])), 
                GENERATESERIES(0,23)
            ),
            "datetime", [Date] + [Value]/24
        ),
        "datetime",[datetime]
    )

and then a measure like this:

Measure = 
  VAR __dateTime = MAX('Dates and Hours'[datetime])
  VAR __date = DATE(YEAR(__dateTime), MONTH(__dateTime), DAY(__dateTime))
  VAR __hour = HOUR(__dateTime)
  VAR __table = FILTER(ALL('Table'), DATE(YEAR(__dateTime), MONTH(__dateTime), DAY(__dateTime)) = __date && HOUR('Table'[dateTime]) + 1 = __hour)
  VAR __result = MAXX(__table, [time])
RETURN
  __result

[hekutoru]

@Greg_Deckler thank you very much.
almost working. The problem is that when I have more than one day the value is being duplicated for every hour

For example, the same value for 28/03/2023 9:00:00 pm is being duplicated for 27/03/2023 9:00:00 pm, even if I don't have values for that 27/03 day and time

[hekutoru]

thank you, I've already post as text, add some information too