Date Bug in Power BI

[Pipi9]

Hi,

I have noticed a date bug in Power BI Desktop. PowerBI desktop display 1st of Oct incorrectly for certain years.  

1 Oct 1972 and 1 Oct 1978 are displayed and exported as 30 Sep 1972 and 30 Sep 1978 respectively.  I don't seem to be able to insert images.

Our company has contacted Microsoft for this bug. They accepted this bug and fixed in the May 2018 release of the Power BI Desktop. 

[v-shex-msft]

HI @Pipi9,

I can't reproduce your scenario, I try to filter on oct 1st without similar issue.

Please provide more detail content to help us clarify this issue.

E.g.

Desktop version, filter options and formula, relationship, sample data, pbix file, screenshots...

Regards,

Xiaoxin Sheng

[Pipi9]

I don't seem to be able to format the date into DD/MM/YYYY format. I haven't applied any filter.

[v-shex-msft]

Hi @Pipi9,

You can try to use below formula to generate calendar table.

let
    DateList = List.Dates(#date(1970, 1, 1), Duration.TotalDays(Date.From(Date.EndOfYear(DateTime.LocalNow()))-#date(1970,1,1)), #duration(1, 0, 0, 0)),
    AddColumns = List.Transform(DateList, each {_}),
    DateTable = #table(type table[Date=date], AddColumns)
in
    DateTable

Regards,

Xiaoxin Sheng

[Pipi9]

I think this issue specifically impacts on the first Sunday in October in years later than 1972, the year the daylight saving was introduced for time zone ‘(UTC+10:00) Canberra, Melbourne, Sydney’ if automatic adjustments for daylight saving is turned on.

This is clearly related to the daylight saving adjustments applied by Microsoft Windows for time zone ‘(UTC+10:00) Canberra, Melbourne, Sydney’ as the problem is not present for other time zones or if auto adjustment is turned off.   

Rules applied by Microsoft are not correct as the daylight saving in this time zone used to start on the last Sunday in Oct prior to 2008 (not first Sunday in Oct as of now) and it should starts at 2:00 am not 12:00am.

[Pipi9]

When exported,  for these year with incorrect date, the time is shifted back by one hour. I think it might have something to do with daylight saving that started from 1972. See https://www.timeanddate.com/time/change/australia/sydney?year=1989

[Pipi9]

[Pipi9]

I have tried your code and still failed miserablly. When I dragged the 'Date' column into a table visual, the date field was automatically changed to Date Hierarchy. However if I remove the hierarchy, it becomes incorrect.

[Pipi9]

Can you please run this code then drag the date into a table visual? Also I am in Australia.

let
DateList = List.Dates(#date(1970,1,1), (Date.Year(Date.EndOfYear(DateTime.LocalNow()))-1970)*366, #duration(1,0,0,0)),
AddColumns = List.Transform(DateList, each {_}),
DateTable = #table(type table[Date=date], AddColumns)
in
DateTable