Filter Max hour by Date

[GustavoKamchen]

Hello everybody.

I want to filter the table below to show me the column "Production" for the maximum hour of every day. How can I do this?

Date	Hour	Production
01/01/2022	05:00:00	10
01/01/2022	06:00:00	20
01/01/2022	07:00:00	30
01/01/2022	08:00:00	40
01/01/2022	09:00:00	50
01/01/2022	10:00:00	60
01/01/2022	11:00:00	70
01/01/2022	12:00:00	80
01/01/2022	13:00:00	90
01/01/2022	14:00:00	100
01/01/2022	15:00:00	110
01/01/2022	16:00:00	120
01/01/2022	17:00:00	130
01/01/2022	18:00:00	140
01/01/2022	19:00:00	150
01/01/2022	20:00:00	160
01/01/2022	21:00:00	170
01/01/2022	22:00:00	180
01/01/2022	23:00:00	190
02/01/2022	05:02:03	9
02/01/2022	06:01:59	18
02/01/2022	07:01:55	27
02/01/2022	08:01:51	36
02/01/2022	09:01:47	45
02/01/2022	10:01:43	54
02/01/2022	11:01:39	63
02/01/2022	12:01:35	72
02/01/2022	13:01:31	81
02/01/2022	14:01:27	90
02/01/2022	15:01:23	99
02/01/2022	16:01:19	108
02/01/2022	17:01:15	117
02/01/2022	18:01:11	126
02/01/2022	19:01:07	135
02/01/2022	20:01:03	144
02/01/2022	21:00:59	153
02/01/2022	22:00:55	162

[PVO3]

If you would like to create a table:

Table =

SUMMARIZE(

    Data,

    Data[Date],

    "Hour",

    MAX(Data[Hour]),

    "Production",

    CALCULATE(MIN(Data[Production]), SELECTEDVALUE(Data[Date])=Data[Date], MAX(Data[Hour]) = Data[Hour])

)

Result:

You might want to format or change the hour column according to your needs.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-shex-msft · Accepted solution]

HI @GustavoKamchen,

You can create a calculated column with the following formula to use the current date to find 'hour', then use the above 'hour' and current date to get the corresponding Production values.

formula =
VAR _maxhour =
    CALCULATE (
        MAX ( Table[Hour] ),
        FILTER ( Table, [Date] = EARLIER ( Table[Date] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table[Production] ),
        FILTER ( Table, [Date] = EARLIER ( Table[Date] ) && [Hour] = _maxhour )
    )

EARLIER, EARLIEST – DAX Guide - SQLBI

Regards,

Xiaoxin Sheng

[GustavoKamchen]

How can I calculate average of daily production?

[Burk]

Hello,

I wrote there my question. https://community.powerbi.com/t5/Desktop/Show-only-data-from-the-latest-date/m-p/2852862/highlight/t...

So i saw yor codes and i have tried but nothing happened. There was no error. 

Can you help me?

note: I don't want to see earlier dates, i need last date and hour line.

Thak you