coldest date in time period based on temperature column

[pbrosnan]

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.

[some_bih]

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!

[some_bih]

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])

[pbrosnan]

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. 

[some_bih]

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

[pbrosnan]

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.

[v-zhangti · Accepted solution]

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.