Forum Discussion
Auto Refresh on Power BI Service changes my chart values
Hi Anonymous, The issue is now resolved. I tried the method again from the YouTube video, by following everything on this video except for one of the steps where I found out in the comments section that I needed to replace the step "DateTimeZone.ToLocal" to "DateTimeZone.SwitchZone-([UTC-ColumnName_UTC],0).. To always display the information in the time zone it was collected. Thanks!
You should not. Instead, make sure all your datetime values are in UTC as well, and leave it up to the users' browsers to convert to the local timezone.
Hi Ibendlin, SowmiSakthi21 and I are working together to fix the issue. In the Power BI desktop, we added three calculated columns to get the date times values in UTC as you suggested.
TIME_OPENED_UTC = 'ORG1 HD_TICKET'[TIME_OPENED] + TIME(5, 0, 0)
TIME_CLOSED_UTC = 'ORG1 HD_TICKET'[TIME_CLOSED] + TIME(5, 0, 0
TIMESTAMP_UTC = 'ORG1 HD_TICKET_CHANGE'[TIMESTAMP] + TIME(5, 0, 0)
After that, we updated the formulas to get the Response Time and Resolution Time:
First_Response_Duration =
DATEDIFF(
'ORG1 HD_TICKET'[FirstResponseTime_UTC], 'ORG1 HD_TICKET'[TIME_OPENED_UTC],
HOUR
)
ResolutionTime =
AVERAGEX(
FILTER(
'ORG1 HD_TICKET',
'ORG1 HD_TICKET'[HD_STATUS_ID] = 2 && 'ORG1 HD_TICKET'[HD_QUEUE_ID] = 1
),
DATEDIFF('ORG1 HD_TICKET'[TIME_OPENED_UTC], 'ORG1 HD_TICKET'[TIME_CLOSED_UTC], HOUR)
)
Avg_First_Response_Time_Per_Owner =
AVERAGEX(
FILTER(
'ORG1 HD_TICKET',
NOT(ISBLANK('ORG1 HD_TICKET'[First_Response_Duration])) -- Exclude tickets with blank duration
),
'ORG1 HD_TICKET'[First_Response_Duration] -- Calculate average for first response duration
)
We implemented these changes, and the chart values were updated to UTC, as shown in the second screenshot we provided in the first post. However, when we published the new report and opened Power BI in the browser, performing a "Refresh now" did not convert the charts to our local timezone.
We appreciate your assistance!