Calculated Column to get Hourly Grouping is messing with the TimeZone Settings

[Anonymous]

Hi All!

I have reporting built off a PBX CDR that stores all times in Australian EST. This is fine.

The issue is, I have created a calculated column to convert the date/time into an Time formated as an hour. this is so I can report on what Hour each call occured in. This is needed for various charts/graphs where we want to track volume per hour. The problem is, any charts using this calculated column are in Australian WST (because I am located in WA I guess where i am creating these datasets in PBI Desktop). But I do not want this. I want the PBX CDR times to be preserved.

Time = CONCATENATE(FORMAT(QueueQuery[Date],"hh") , ":00")

I hope this makes sense? Times are honoured in the reporting on the PBI Service for everything else, it seems to be anything that uses this calculated column that is reverting to my time zone.

How can I have this calculated column that shows the hour, but have the service honour the times as they are in the CDR?

[lbendlin]

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

[Anonymous · Accepted solution]

Hi!

Actually it wasnt this; i worked it out. It was my own fault. Will explain in case anyone else does the same thing:

1. I am using a postgresql DB as the source for my reporting
2. I pull data from here for my PBI reports in the format i want to use, using an SQL query
3. For some reason, in my query, i was pulling a date from my SQL DB, casting it as text, then converting it to a datetime. 
4. taking the date (which was EST) from the DB, and then converting it again to a datetime on my local machine (WST) was making it change to WST.
5. I simply removed the cast and conversion on my SQL as i had no need to do that.
6. Problem solved!

It's ALWAYS something stupid. hehe.