The data type time works with greater than and equal but are incompatible in the less than operator?

[Peter_B]

Hi,

i'm having problem with not being able to use the less than operator when counting rows and the selected column is in the TIME-format.

HH:mm:ss

> Works fine:

Measure = COUNTAX(FILTER('KPI';'KPI'[Created]>TIMEVALUE("17:00:00"));'KPI'[KPI])

= Works fine:

Measure = COUNTAX(FILTER('KPI';'KPI'[Created]=TIMEVALUE("17:00:00"));'KPI'[KPI])

< Does not work:

Measure = COUNTAX(FILTER('KPI';'KPI'[Created]<TIMEVALUE("17:00:00"));'KPI'[KPI])

Error message:

The data types datetime and time are incompatible in the less than operator.. The exception was raised by the IDbCommand interface.

Does anyone know how I can get the less than to work in the same manner as greater than or equal?

Regards,

Peter

[v-sihou-msft]

@Peter_B

What's your data source?

As I know, this is a limitation for SQL Server 2012 or later version. To workaround this issue, I suggest you retrieve the time part from your datetime and directly compare the time values when using "less than" operator.

Measure  =
COUNTAX (
    FILTER (
        Table1,
        TIME ( HOUR ( KPI[Created] ), MINUTE ( KPI[Created] ), SECOND ( KPI[Created] ) )
            < TIMEVALUE ( "17:00:00" )
    ),
    KPI[KPI]
)

Regards,