Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello experts
Long term reader, first time poster! I'm currently working on a couple of projects that pull data from SharePoint Online lists. I'm able to pull all this thorugh to Power BI without any issues, the problem is with the dates. There are a few similar questions on here but none of the solutions seem to resolve my particular issue.
I'm in the UK so both SharePoint and Power BI timezones are set to UTC, however we're currently in daylight savings so are an hour ahead. The date/time entered in SP is, for example, 26/09/2018 15:00 but appears in PBI as 26/09/2018 14:00. It looks to me like SP is converting the data from UTC+1 to UTC then PBI is reading it and not adjusting back. For data entered before the clocks went forward the time is pulling through correctly.
Is there any way to fix this? I can obviously add an hour on but as it's only affecting half the year, at a point which will change year on year I don't really want to do this.
Thanks!
I had this problem when using SharePoint as a datasource for my PowerBI report. Each date in my Sharepoint List was entered as the first of each month (01/07/2022), but for some months, when it came through to PowerBI it was showing (30/06/2022).This was due to the Daylight Savings Time.
My approach to this little problem is a bit "Heath Robinson" but it worked for me.
Duplicate the date Column (which should be in a Date/Time/Timezone format) and then Split the new colulmn by a delimiter of +. This gives you two columns, the second of which is either 01 or 00 (you can delete the first column now as it is redundant).
format the second column as an integer which gives you either 0 or 1 (this gives you an offset).
You can then add that offset in a new calculated column to the original date field by using Date.AddDays
boom - newdate which is now correct
Hope this helps!
HI @mnb,
Maybe you can add a custom column in query editor with if statement to check current date time( if it in specific data range) and add offset.
In addition, you can also take a look at following blog:
Daylight Saving Time And Time Zones In M
Regards,
Xiaoxin Sheng
Thanks for the reply @v-shex-msft. I can see how this would work for a manual refresh but once I deploy to the server and refresh the data there it will use the server time which as I understand it is UTC year round so there would be no impact. Unless I've misunderstood?
HI @mnb,
>>I can see how this would work for a manual refresh but once I deploy to the server and refresh the data there it will use the server time which as I understand it is UTC year round so there would be no impact.
AFAIK, power bi service will convert datetime to UTC formula, it not support analysis by local timezone, you can take a look at following link to know more about this:
Time in PBI Service is inconsistent with the local time (non-UTC time) displayed in PBI Desktop
Regards,
Xiaoxin Sheng
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |