Forum Discussion

Frequent Visitor

3 years ago

Solved

Filter Max hour by Date

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 1...

Need Help

Anonymous
3 years ago
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

Anonymous

Not applicable

3 years ago

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