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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Min, max and average by date

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.

1 ACCEPTED SOLUTION
vicky_
Super User
Super User

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:

vicky__1-1703197449737.png

 

View solution in original post

1 REPLY 1
vicky_
Super User
Super User

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:

vicky__1-1703197449737.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.