Power BI Service UTC Date

Power BI
1

Hi everyone, im struggling with PowerBI service. The result is that the Calendar table is setting date columns as UTC. The impact is that reports I have showing revenue by month is being offset by an hour because my time zone is GMT.

To expand on the issue. I have a table of contracts. These have start and end dates, monthly revenue amounts and the contracted product. I use a function to calculate the pro rated monthly revenue. The function creates a row for every month with a pro rated factor and the month date. I use that date to plot the contract revenue on a matrix. Cols = Months, Rows = Product, Values = Measure (Revenue * Pro Rated Factor). I have a contract that starts on the 1st Oct 2020. This issue causes 1 hours revenue to be reported in Sept.

I can understand I need to handle localisation when dealing with datetime’s but in my case I only go to date level. Im assuming its a fault in the Service but wondered if anyone else has experienced this issue and has a solution?

2

Hi @aTChris. Its not a fault of the PBI Service, but rather a characteristic. All datetime values are represented in UTC in the service. Are your fields in question stored as [Date/time] or as [Date]? Greg

3

Hi @Greg Everything related to this is stored as a date. I would expect it if I was working with date/time. I’ve checked and at no point during translation were any of the fields related to this stored as a date/time.
Thanks Chris

4

Hi @aTChris,

Welcome to the Forum!

You could test that by adding a Local TimeZone column to your Calendar table.
For example +1 hour difference = DateTime.AddZone( [YourDateColumn], 1)

5

Hi @aTChris,

You can also have a look at these blogs.

Regards,
Harsh Nathani

6

Hi @Melissa, thanks. It’s good to be here.

I think your suggestion is a nice workaround… until 25th Oct when BST reverts to GMT. I have a thread with MS on the PBI Forum as well. I will see what they report on this one. I shouldn’t really have to build in timezone support for a date field.

7

@harsh.nathani thanks for those posts.

They are all referring to time zone issues with date/time functions which shouldn’t be a factor in my case as im only handling a date.

8

Hi @aTChris,

Yeah agree with you there…

Please report back if you receive a solution via the MS route, that way other members can benefit from that in future should they encounter a similar issue. :wink:

9

@Melissa found it… totally my fault but really hard to spot as you cant inspect the tables in the service. I removed the localisation of the datetimezone by formatting it as a date. Which is fine locally but on the service that would cause the 1hr offset therefore shift my startDates back by and hour therefore a day.

I applied datetimezone to all fields and its consistent across all platforms.

1 Like