Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to add to my reports the date and time when report was refreshed.
I have found the approach, but the problem is that in Power BI Service time zone is different, so I need to adjust it.
I have tried this:
let
Source = #table(type table[Date Last Refreshed=datetimezone], {{DateTimeZone.SwitchZone(DateTime.LocalNow(),3,0)}})
in
Source
Get error:
Solved! Go to Solution.
Hi @sandra_p ,
You may select on the new created datetime column, go to Transform tab, choose Data Type and select Date/Time/TimeZone instead of Date/Time, don't forget to click button "Close & Apply".
For reference:
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328
https://radacad.com/solving-dax-time-zone-issue-in-power-bi
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Sandra,
Your best shot is to take the UTC time, and convert it to your desired daylight savings time as described here: https://gorilla.bi/power-query/last-refresh-datetime/
That works with this logic:
let
UTC_DateTimeZone = DateTimeZone.UtcNow(),
UTC_Date = Date.From(UTC_DateTimeZone),
StartSummerTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
StartWinterTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
CET_Timezone = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
CET_Timezone
The important aspects here are:
Returning a fixed UTC datetimezone value, regardless of the refresh location of the server. Details here:
https://powerquery.how/datetimezone-utcnow/
And then switching the datetimezone value to the right zone as described here:
https://powerquery.how/datetimezone-switchzone/
Hope that helps - Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
@sandra_p , can share the sample data . If can you are UK data format, make sure setting is correct
Hello,
Thank you for answer.
Everything is ok with format. I need to see the time 3 hours ahead in Power BI Service.
On my computer time is correct, because I have my local time zone on it.
Once report is published - time is 3 hours back once report is refreshed.
This function https://docs.microsoft.com/en-us/powerquery-m/datetimezone-switchzone - is supposed to switch timezone - that is what I need. But once I use it - it throws error (mentioned in my post) even though - the field I pass in it (see my code) is DateTimeZone type.
Hi @sandra_p ,
You may select on the new created datetime column, go to Transform tab, choose Data Type and select Date/Time/TimeZone instead of Date/Time, don't forget to click button "Close & Apply".
For reference:
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328
https://radacad.com/solving-dax-time-zone-issue-in-power-bi
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |