Power BI Service Time Differences and How to Fix It

For those of you who have published reports with a refresh date/time and have been frustrated with the results showing the time is totally incorrect then I have just the solution for you. Before we get to the solution let's look at what the issue is.

Every Power BI Server (Where the Power BI Service lives) follows UTC (Coordinated Universal Time) for the server time. This is the universal time standard. You may have noticed that when you first publish your report the time stamp listed is correct it is only when you refresh that you see a difference. The reason for this is that Power BI refreshes time based on the localized time of the machine in which the report is refreshed on. So, refreshing on your local machine results in local time whereas refreshing on the services results in UTC.

So how can we solve this? There are a few ways. The easiest one is by creating a Calculated Column (Right click on the table you want this to reside in then click New Column) taking the current date/time and adding or subtracting (depending on the timezone logic) the division of the timezone by 24:

DAX LOCAL TIME = 
    NOW() - ( 5 / 24 )

Another way is to use Power Query and create a Blank Query. Click Transform Data, click New Source and select Blank Query.

In the formula bar (white bar) add the following code:

=DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5,0)

If you're missing the the formula bar you can add this by clicking View then checking the box for Formula Bar

Both of these ways work fine... for now. However, they both have one major flaw. That flaw is what happens when Daylight Savings Time occurs. See, these functions do not take Daylight Savings Time into account. So we know we just recently had the Spring Forward event which occured on March 14th. We stay in this mode until November 7th when we Fall Back. So, currently, these functions will work fine until November 7th through March 13th. Then your time will be off. So, what can we do about this conundrum? To remedy this we need to get a bit more advanced. So similar to the second function we start by creating a Blank Query in Power Query, clicking Advanced Editor

then pasting the following code:

let


date = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5,0)),
time = DateTime.Time(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5,0)),
//negative five (-5) is based off of CST adjust this on date and time to your timezone.


firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),


isSummerTime =	(date = SecondSundayOfMarch and time >= #time(1,0,0))
	        or
		(date > SecondSundayOfMarch and date < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (Number.From(isSummerTime))*1 - 1, 
//negative one (-1) may need to be adjusted depending on your timezone. 


ltime = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)


in

    ltime

With this we accomplish it all and future proof our date/time refreshes for Daylight Savings Time. #PowerBI #PowerQuery #DAX #MCode #ProblemSolving #tutorial