Forum Discussion
Date Bug in Power BI
I don't seem to be able to format the date into DD/MM/YYYY format. I haven't applied any filter.
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
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.
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
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.