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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.