Min and Max Columns summarising multiple rows

[Anonymous]

Hi all, 

I have a large dataset of half hourly temperatures for two decades. I have created a column which summarises the same days across the multiple years, from 1 to 366 ("Day" column). I need to now calculate the minimum temperature and maximum temperature for each Day (1 to 366). 
I tried a simple DAX Calculate Min filtering by Day but it has not summarised as I wish it to. Further when I did the same for DAX Calculate Max filtering by Day, a circular dependency error occurred. 

A table in the report view does this perfectly, however I need to calculate the average between the min and max value for each Day, which I understand has to be computed based on Columns. 

Thank you

[v-shex-msft]

HI @Anonymous,

You can try to use following calculate columns formula to get the max and min temperature based on current date:

daily MinT =
CALCULATE (
    MIN ( Table1[Temperature] ),
    FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) )
)

daily MaxT =
CALCULATE (
    MAX ( Table1[Temperature] ),
    FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) )
)

Regards,

Xiaoxin Sheng

[barritown · Accepted solution]

Hi @Anonymous,

If I've correctly understood your task, you could solve it with the help of the three measures below and a table visual:

MinTemp = MIN ( [Temperature] )

MaxTemp = MAX ( [Temperature] )

AvgTemp = ( [MaxTemp] + [MinTemp] ) / 2

In the test case below you can see dates, but it will work with days too:

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian