filter a table to get times in certain range

[po]

Hi,

Have a table with dates in it (date and time) how can we best filter this table to only include those rows where the time is between midnight and 10.00 a.m and then get the maximimum of those rows?

Thanks 

[v-cherch-msft]

Hi @po 

You may try below measures:

Filter =
IF (
    MAX ( 'Table'[Time] ) <= TIME ( 10, 0, 0 )
        && MAX ( 'Table'[Time] ) >= TIME ( 0, 0, 0 ),
    1
)

Max_time =
VAR max_time =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Date] ), [Filter] = 1 )
    )
RETURN
    IF ( MAX ( 'Table'[Time] ) = max_time, MAX ( 'Table'[Time] ) )

Regards,

[po]

Hi 

Thanks for reply.

Aplogies - I didn't make clear the data and time are one column e.g 01/05/2019 09:00:00

Thanks

[v-cherch-msft · Accepted solution]

Hi @po 

You may create date and time columns as requested.Then use the above mentioned workaround.

Regards,

[po]

Thanks for update