How to compare time in DAX ?

[RishikeshD]

I have written following DAX code to compare if vehicle has arrived on time ( set at 12 here) . I have an Inward Table wgich has Inward Time Column of Type Short Time .

Vehicle Arrival Window Comformity = COUNTX(inward,(inward[Inward Time]>FORMAT("12:00","Short Time")))

But I getting following error :-

Error Message:
MdxScript(Model) (14, 69) Calculation error in measure 'employeemaster'[Lifting Distance Comformity]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[v-xinruzhu-msft · Accepted solution]

Hi @RishikeshD 

You can try the following code:

Vehicle Arrival Window Comformity = COUNTX(inward,TIMEVALUE(inward[Inward Time])>TIMEVALUE("12:00"))

Best Regards!

Yolo Zhu

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

[ppm1]

Please see this article. When dealing with dates/times, it is a good practice to handle them as the numbers they are behind the scenes, and only format/display your result at the end.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[AlexisOlson]

FORMAT returns text. Try using TIME or TIMEVALUE.

TIME ( 12, 0, 0 )
TIMEVALUE ( "12 pm" )