Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
vissvess
Helper V
Helper V

Convert Timezone of timestamps

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.

8 REPLIES 8
amitchandak
Super User
Super User

@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.

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

?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

This breaks the live query.

Please find the image below.

vissvess_0-1599579693621.png

 

 

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@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?

 

@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. 😕



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.