Dates, Highs and Lows

[Syndicate_Admin]

Hello, I need please if you can help me with the following:

I have several teams that have the name of the letter A to Z, each one is distributed in a geographical area and its function is to take the temperature of the place every day and every year.
Each computer keeps a daily record with the following data: the name of the equipment, the date and the temperature of that day.

Example:

What I need is to take out a matrix where it shows me the maximum and minimum temperature of all the months of the year of each team and also must show the date of that maximum and minimum.

Something like this:

I tried to take it out but I can't, could you help me please?

Thanks a lot.

[v-rzhou-msft · Accepted solution]

Hi @Syndicate_Admin ,

Here I create a sample to have a test.

Data model:

Measure:

Fecha de Temp Max = 
VAR _MAX =
    MAX ( 'Table'[Temperatura] )
VAR _DATE =
    CALCULATETABLE (
        VALUES ( DimDate[Date] ),
        FILTER ( 'Table', 'Table'[Temperatura] = _MAX )
    )
VAR _COMBINE =
    CONCATENATEX ( _DATE, [Date], "
" )
RETURN
    _COMBINE

Fecha de Temp Min = 
VAR _Min =
    MIN ( 'Table'[Temperatura] )
VAR _DATE =
    CALCULATETABLE (
        VALUES ( DimDate[Date] ),
        FILTER ( 'Table', 'Table'[Temperatura] = _Min )
    )
VAR _COMBINE =
    CONCATENATEX ( _DATE, [Date], "
" )
RETURN
    _COMBINE

Max Temp for each Equipos = CALCULATE(MAX('Table'[Temperatura]),ALLEXCEPT('Table','Table'[Equipos])) 

Fecha de Temp Max for each Equipos = 
VAR _DATE =
    CALCULATETABLE (
        VALUES ( DimDate[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Equipos] = MAX ( 'Table'[Equipos] )
                && 'Table'[Temperatura] = [Max Temp for each Equipos]
        )
    )
VAR _COMBINE =
    CONCATENATEX ( _DATE, [Date], "
" )
RETURN
    _COMBINE

Result is as below.

You can download my sample file to learn more about measures.

Best Regards,
Rico Zhou

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

[Syndicate_Admin]

If I had to get the date of the maximum temperature value, how would it be?

Thank you.