Convert Timezone of timestamps

[vissvess]

Hi Community,

I have the data in SQL that stores timestamps. The timestamps are in GMT without any time zone information.

I want to consume the data in a live query report where I want the timestamp in IST (+5.5 hrs).
If I add the duration to it, the live query breaks.

Please give me a solution for this.

[amitchandak]

@vissvess , refer TimeZone
https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
https://www.youtube.com/watch?v=M1zquwmpnZE

[vissvess]

@amitchandak ,

All the solutions you have suggested will work good for Import mode dataset.
My query is specifically for Live query dataset, where I am looking for some power query transformation which doesnot break into import mode.!!

@Greg_Deckler, I was using a calculated column with the same DAX, but as a effect of using calculation groups, the calculated column is incompatible with calculated columns in live query.I am now forced to do it in the power query.

[Greg_Deckler]

@vissvess - In Power Query, can you use [Column] + #duration(0,5,30,0)

?

[vissvess]

Hi @Greg_Deckler ,

This breaks the live query.

Please find the image below.

[Greg_Deckler]

@vissvess - Your only bet may be to add a column in SQL. One thing, the DAX way, you referred to calculated columns, did you mean measure? Because it was meant to be a measure as I didn't think you could do calculated columns in Live query. Is this Live query or Direct Query? There is a big difference. Your image shows that it is DirectQuery, not a Live connection.

[vissvess]

@Greg_Deckler , yes you are right. it is direct query mode.
I forgot the live connection and I did not mean that. Sorry.

We can have calculated columns in direct query and I prefer calculated columns for this over measure as I can use the column in filter.

So, hope I am clear. Is there any workaround or solution for me to achieve this?

[Greg_Deckler]

@vissvess Yes, OK, you can have a calculated column in Direct Query mode but this is going to force you into a composite model, which can cause issues. And it seems you were possibly hitting some of those issues? You can use a measure in a filter (Filter pane) of visual level filters. Of course, if you go the measure route and wanted to use it in a slicer, you would need a disconnected table which would force you back into composite mode. 😕

[Greg_Deckler]

@vissvess - How are you trying to add the +5.5 hours? Are you using a measure like: 

Measure = MAX('Table'[timestamp]) + 1/24 * 5.5