DateTimeZone, One would think that Microsoft would make this easier to manage in the PBI Service. I’m in Australia (UTC +10) Here’s my issue, no matter what I try in Power Query, I cannot get the dates to display correctly for the current day in Australia. I’m using a few visuals to display the sales for the current date and the day before but they do not update to the correct date in AU until 10:00 local time (00:00 UTC). I’ve tried everything I’ve used in other models - no joy. There are numerous posts and videos out there about this but nothing to directly address the time zone problems globally in the PBI service. Yes, I’ve set the locale in both PBI Service and the PBI Desktop. I am using @Melissa dates table from a post here in the forum.
Appreciations in advance!
I have a dates table that contains a standard ‘Date’ column and ‘Current Date’, ‘Yesterday’, DayOffset.
#“Added Current Date” = Table.AddColumn(#“Renamed Columns”, “Current Date”, each Date.From(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),10,0))),
#“Added IsCurrentDay” = Table.AddColumn(#“Added Current Date”, “IsCurrentDay”, each if [Date] = [Current Date] then “Current Date” else “Other Date”),
#“Added Yesterday” = Table.AddColumn(#“Added IsCurrentDay”, “Yesterday”, each Date.AddDays(Date.From(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),10,0)),-1)),
#“Added Today-Yesterday-Other” = Table.AddColumn(#“Added Yesterday”, “Today-Yesterday-Other”, each if [Date] = [Yesterday] then “Yesterday” else if [Date] = [Current Date] then “Current Day” else “Other Day”)