Min, max and average by date

[Anonymous]

I have two columns in Power BI. One is called 'Date' and the other is called 'AIRTEMP.' In the 'Date' column, it has hourly values ​​for the day, and the 'AIRTEMP' column contains these values ​​according to the date/time of the record. For example:

01/11/2023 00:00     23,23
01/11/2023 01:00     22,98
01/11/2023 02:00     23,07
01/11/2023 03:00     23,07
01/11/2023 04:00     23,23
01/11/2023 05:00     22,88
01/11/2023 06:00     22,26
01/11/2023 07:00     21,56
01/11/2023 08:00     22,55
01/11/2023 09:00     23,77
01/11/2023 10:00     25,65
01/11/2023 11:00     26,57
01/11/2023 12:00     26,93
01/11/2023 13:00     27,92
01/11/2023 14:00     28,73
01/11/2023 15:00     30,86
01/11/2023 16:00     30,17
01/11/2023 17:00     28,02
01/11/2023 18:00     26,04
01/11/2023 19:00     25,03
01/11/2023 20:00     24,75
01/11/2023 21:00     24,37
01/11/2023 22:00     24,26
01/11/2023 23:00     24,28

I need to create a formula that returns the lowest value of the day, the highest value of the day, and the average value of the day.

[vicky_ · Accepted solution]

If you're just looking to display the data in a table, then there's no need to create a DAX formula. You can use the built-in aggregations, by first:

Add a new column to your table to change the dateTime to date, you can do this with the formula:

Date = INT(Table[DateTime])

and changing the type to Date.

next, using a table visual, drag the date (right-click and make sure that "Do not summarize" is selected), then the Value column 3 times. For each of the Value fields, you can then right-click and choose a type of aggregation: