Date timezone changing between Query Editor and Desktop

Hi

I have come across an issue with my Power Bi report where the date is changing when my data is applied from the query editor into the desktop report.

In the example below the date this item was created in our database was 01/07/2020 12:00:00 AM + 12:00 which is correct as per our data base, but when i apply the query to my report and filter on that same date field the date for that item changes to the day before e.g 30/06/20.

I have checked my regional settings and it is set correctly to English New Zealand?

Hi Melissa @Melissa

This is the Mcode date table i am using.

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, “YearNumber”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,“MonthnYear”, each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,“QuarternYear”, each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“QuarternYear”, Int64.Type},{“Week Number”, Int64.Type},{“Year”, type text},{“MonthnYear”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“MonthOfYear”, Int64.Type}, {“QuarterOfYear”, Int64.Type}, {“MonthInCalendar”, type text}, {“QuarterInCalendar”, type text}, {“DayInWeek”, Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, “ShortYear”, each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, “FY”, each “FY”&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable

HI @AoakeP,

Yeah we probably won’t find the solution there :thinking: (I misread your initial post…)

Q. Does the “Date problem” occur in the Power BI Service or in Power BI Desktop?
If the problem is in Desktop it could have to do with your System settings, have you checked that?

Q. Does your database add the +12:00 at the end because I looked up NZDT to UTC time difference and that’s 12 hours too

Hi @AoakeP

If you are just using the date as a reference for yourself on when the table is created to ensure you are using the most up to date information.

Would it really matter. Are you using that “creation date” in some kind of calculation?

I’m trying to learn as much as possible in the forum

I"m just wondering.
thanks
Keith

Hi @Melissa and @Keith

The problem occurrs in the Power Bi Desktop, the date in the Power Query table is accurate but when the data is applied to the desktop and i create a table with the same fields to check the data the date has changed to the day before. I checked the Regional settings for both the Power Query and Desktop and they are both set to English New Zealand which is our correct timezone.

Another factor that might be useful in trying to determine why this is happening is our data is held on premise Dynamics CRM database in Canada, we access the data through the same Dynamics CRM database in New Zealand, the dates we see in the database reflect our timezone.

@Keith its important that the dates are reflected in the correct period as it makes a difference in frequency rate calculations that are part of our companies KPI’s so that data needs to be reflected in the correct month

Seems like you don’t really need the the time portion…
If the Date alone is sufficient go to Transform/Date/Date only

Hi @AoakeP,

Ok, i just thought it was the creation of the date table not the actual data coming from the system.

Does it happen every time you download the data or only at the end of the month?

I would say the creation of the file from the system would have to be July 1 at 12 am for June 30. If the file creation is run before 12 am on July 1 then you would likely be missing June 30 transactions.

I don’t know if that make sense.
thanks
Keith

Hi @AoakeP, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!