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?