TIME(3, 0, 0) Function adds days to datetime object instead of just hours.

[gieloo]

Hey Microsoft and others,

Yesterday I stumbled upon strange behaviour from the TIME() function in Power BI. I have a column with timestamps represented in the following format: 6/22/2022 8:03:01 PM and want a new column that contains this time converted to UTC (adding 3 hours). Note: the data comes from direct query so I can not change the timezone during data transformation.

What I did:

1. Create column
    TimeUTC = MyTable[TimeStamp] + TIME(3,0,0)

    Example value: 6/22/2022 8:03:01 PM

2. Check new column

    Transformed example value: 6/24/2022 11:03:01 PM

The TIME() function documentation states that the syntax is TIME(hour, minute, second). However, when I use timestamp + TIME(3, 0, 0) it also adds two days to my timestamp. 

I fixed it by changing TIME() to ((1/24) * n_hours) where n_hours is 3, but I feel like TIME() should do the job equally well.

[v-cazheng-msft]

Hi @

When testing it in Import data, it works well.

Just as the official document said, TIME function is not supported for use in DirectQuery mode when creating calculated column. Fore more details, you could refer to TIME function (DAX) - DAX | Microsoft Docs.

\

Best Regards,

Community Support Team _ Caiyun