Time + 6 hours

[Anonymous]

Hi,

I have log files that show US server time (-6) so I need to add 6 hours to the time column in my table.

I have  a "Tijd VS" column that shows the date and time (e.g. 31-8-2018 0:00:00 --> A date/time column in Power BI)

I tried to add a new measure --> Tijd = 'EZproxy log'[Tijd VS]+ TIME(06;00;00)

But I got the error: A single value for column 'Tijd VS' in table 'EZproxy log' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Does it go wrong because I have a date/time column? I also tried the same formula with my date/time column separated in a date and a time column. In that case the +6 hours work but Power BI adds a date to it (e.g. 30-12-1899 6:00:00) which is also not correct.

What am I doing wrong? And how can I fix it?

[vishnurk]

Could you check if the below one is helpful

https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328

[Anonymous]

Hi,

Get this error when I try it as new measure.

So no, doens't work.

The syntax for '.1' is incorrect. (DAX(FORMAT('EZproxy log'[Tijd VS],"MM/dd/YYYY") & " " & REPLACE(FORMAT('EZproxy log'[Tijd VS],"HH:mm:ss").1.2,HOUR('EZproxy log'[Tijd VS])+6))).

[Baskar]

[Anonymous]

@Baskar

It does work if you replace the , by  ;

However it also introduces a new problem. 

If I want to change anything in an existing column via edit queries I get an error back that there are more columns than expected.

[Chihiro · Accepted solution]

Why not add the column in Query Editor instead of in DAX?

Add Custom Column:

[TimeColumn] + #duration(0,6,0,0)

[Anonymous]

Many thanks for the help!

[Anonymous]

Hi @Baskar,

I used Tijd = 'EZproxy log'[Tijd_VS] + TIME(6,0,0) as a new column and got this error: The syntax for '.0' is incorrect. (DAX('EZproxy log'[Tijd_VS] + TIME(6.0.0))).

[v-danhe-msft]

Hi @Anonymous,

You could try this formula:

Measure = CALCULATE(SUM(Table1[Tijd VS]))+6/24

Result:

You could also download the pbix fiel to have a view.

Regards,

Daniel He

[vishnurk]

Hi,

Could you use it as a calculated column instead of measure,like below

[Anonymous]

Hi @vishnurk,

I added a calculated column with: Tijd = FORMAT('EZproxy log'[Tijd_VS];"MM/dd/YYYY") & "  " & REPLACE(FORMAT('EZproxy log'[Tijd_VS];"HH:mm:ss"),1,2;HOUR('EZproxy log'[Tijd_VS])+6)

And got this error: The syntax for '.1' is incorrect. (DAX(FORMAT('EZproxy log'[Tijd_VS],"MM/dd/YYYY") & " " & REPLACE(FORMAT('EZproxy log'[Tijd_VS],"HH:mm:ss").1.2,HOUR('EZproxy log'[Tijd_VS])+6))).