topic Re: coldest date in time period based on temperature column in DAX Commands and Tips

coldest date in time period based on temperature column

pbrosnan — Thu, 21 Sep 2023 11:22:36 GMT

how would i do this in Power BI Dax?

I have the following table containing 2 columns:

Date,Temperature

which contains multiple temperatures per day so multiple rows per day in the table

I am looking for the coldest date in 2022 and 2023 based on the temperature column. please advise.

Re: coldest date in time period based on temperature column

some_bih — Thu, 21 Sep 2023 11:44:29 GMT

Hi pbrosnan try with following measures per years. For Max date, just replace MINX with MAXX

Coldest_Date_year_2022 = MINX(
FILTER(yourtablename, YEAR(yourtablename[Date]) = 2022),
yourtablename[Date])

Coldest_Date_year_2023 = MINX(
FILTER(yourtablename, YEAR(yourtablename[Date]) = 2023),
yourtablename[Date])

Re: coldest date in time period based on temperature column

pbrosnan — Thu, 21 Sep 2023 11:49:16 GMT

i need the date filter to be variable between 01-01-2022 and 31-12-2023. as i am using a date slicer as the filter.

Re: coldest date in time period based on temperature column

some_bih — Thu, 21 Sep 2023 11:55:14 GMT

Hi pbrosnan so you want result as calculated column or something else. Please describe.

Re: coldest date in time period based on temperature column

pbrosnan — Thu, 21 Sep 2023 12:01:05 GMT

i have a table containing these rows and columns:

date,temperature

01-01-2021,150

01-01-2021,120

02-01-2021,120

02-01-2021,110

03-01-2022,160

03-01-2022,170

05-01-2022,160

05-01-2022,180

i want to return the date that has the lower temperature which is 02-01-2021 so how do i calculate it? there are multiple temperatures per day.

Re: coldest date in time period based on temperature column

some_bih — Thu, 21 Sep 2023 13:14:15 GMT

Hi pbrosnan possible solution: create 2 measures, one for identification of Min temp and finding date for this Min date. Edit Sheet1 to your table name

Coldest_Temp_year =

VAR __year=2021 -- choose year as you wish

RETURN

MINX ( FILTER ( Sheet1, YEAR ( Sheet1[Date] ) =__year ), Sheet1[Temperature] )

Date of Min temp 2021 =

VAR __daily_temp =

ADDCOLUMNS ( VALUES ( Sheet1[Date] ), "@Daily_temp", [Coldest_Temp_year] )

VAR __min_daily_temp =

MINX( __daily_temp, [@Daily_temp] )

VAR __dates_with_Min =

SELECTCOLUMNS (

FILTER ( __daily_temp, [@Daily_temp] = __min_daily_temp ),

"DateOfMin_temp", Sheet1[Date]

)

VAR __Result =

IF ( COUNTROWS ( __dates_with_Min ) = 1, __dates_with_Min, BLANK () )

RETURN

__Result

For your sample table output is as following

Did I answer your question? Kudos appreciated / accept solution!

Re: coldest date in time period based on temperature column

v-zhangti — Wed, 27 Sep 2023 01:45:12 GMT

Hi, pbrosnan

You can try the following methods.

Measure = Var _mintem=CALCULATE(MIN('Table'[temperature]),ALL('Table')) Return CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),[temperature]=_mintem))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.