Hi Rick, thank you for making the formula available on teh blog, easy to copy and use. I have just implemented it and it was so quick. Thank you. It is great.
This is great. But I believe this is to handle the last refresh date only. I have multiple columns in the dataset that have default data type as Date/Time/Zone and even if I change the data type of the columns to Date, it is changing in the Power BI Service. Can we create a function that adjust the timezone for all columns in my dataset? There are a lot of columns in my dataset and I don't want to transform each and every one separately.
Both Ireland and the Netherlands observe DST. During summer, Ireland is GMT and the Netherlands is GMT+1. During winter, Ireland is GMT+1 and the Netherlands is GMT+2. So the time difference between Ireland and the Netherlands is ALWAYS 1 hour, i.e. the Netherlands is always 1 hour ahead of Ireland. How do you explain that the time jumped 2 hours back after the Power BI Service refresh??
What happens for users in different timezones? This will only work for the offset you statically set. Is it possible time zone offset to be detected from the user browser and time refresh to be correctly shown to users in different time zones?
Hey aleksander, With the video example it only works statically. If you want a dynamic example based on the user location, I imagine you could build a solution in DAX that uses row level security. The row level security table could then use offset values based on the location of your user. I may make a video on this at some point :)
@@BIGorilla thanks, that could work. For public reports (publish to web) probably we need to look for another workaround since detecting web browser data is not an option. btw great videos, keep up with the great work.
@@aleksandarboshevski I haven' t found a good solution for that one. There are solutions out there that call to an API to return the correct daylight savings time. However, that one is also bound to the location where your report is refreshed. And with that it does not solve your challenge.
Dear Rick, great approach, I've been looking for it for a very long time. I have a similar issue with decimal comma in desktop but decimal point in service. In addition thousand seperator point is changed to comma as well. Do you know if there is a solution for that as well ? Your answer will be highly appriciated. Have a nice day.
Hi! yes that's frustrating. To change the decimal separator, a user should login to the power bi service , go to settings, look for Display language and change their language settings in the browser. It's a personal setting that you can set.
Excellent video sir, But one question here, Pls clarify me I have UTC datetime column which contains dates between 2020 to 2030 ( let's assume ), I have to create one custom column which is offset column for both ( Daylight Saving Time = UTC - 5 && Central Standard Time = UTC - 6 ). How to create that offset column based on UTC timestamp column. OR How to create directly a custom column by converting UTC timestamp column to CST and CDT timestamp
Same as in the video. Except change the DateTimeZone.UtcNow() To your DateTimeZone Value in your column. You can take a look at my written post if you like to copy paste it gorilla.bi/power-query/last-refresh-datetime/ Enjoy!
@@BIGorilla correct me if i am wrong. Daylight Saving Time ( Also called Central Daylight Time ) starts every year at 2nd Sunday of March at=2 AM and ends before 1st sunday of November 2 AM.
@@kummithavenkatareddy2302 The example shown uses mostly the dates. You can easily add the time of 2 AM to it. The logic is that wintertime starts the last sunday in October. And summertime the last sunday of march. The code for that is in my blog. You only have to adjust it to add the 2AM. I'm sure you'll manage 🙌🏻🙌🏻
It doesn't work, Your time before refresh was 22:00 hours, after refresh in service you still have 10 pm. I don't think I'm mistaken. But none of my attempts at this resulted in the change. I followed the steps to a T. Has anyone else experience this?
Hi! Your observation is correct, but the conclusion is not. Here's what happened. I uploaded a file with 22:00 hours. When I refreshed the file in the power bi Service, the time swapped to 20:00 hours because the server refreshes in a different timezone. Using the code I provided, you can make sure to always get right time, regardless of the location of the refresh. Hope that clarifies. If it's still unclear, please watch the video again. It is explained in there. Thanks, Rick
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
this is going in my saved bookmarks for BI tricks. worked great thank you!
Well done. This is cleanest implementation of this I’ve seen. Thanks.
Thanks Donald. I feel this should have been implemented by Microsoft in an easy way. But until then, this is one alternative 🚀🚀
Well, this is more complicated than I thought it would be. Thanks for constructing this solution.
Thanks Geert. Once you know the trick it’s a relieve that it keeps on working. Feel free to copy-paste the code on my blog if it helps. 🙌
Great Video! I am defiantly going to apply this to my projects.
Thank you, more on this in the written blogpost gorilla.bi/power-query/last-refresh-datetime/. Enjoy!
Hi Rick, thank you for making the formula available on teh blog, easy to copy and use. I have just implemented it and it was so quick. Thank you. It is great.
Happy to read this!! thanks for sharing
This is great. But I believe this is to handle the last refresh date only.
I have multiple columns in the dataset that have default data type as Date/Time/Zone and even if I change the data type of the columns to Date, it is changing in the Power BI Service.
Can we create a function that adjust the timezone for all columns in my dataset? There are a lot of columns in my dataset and I don't want to transform each and every one separately.
Both Ireland and the Netherlands observe DST.
During summer, Ireland is GMT and the Netherlands is GMT+1.
During winter, Ireland is GMT+1 and the Netherlands is GMT+2.
So the time difference between Ireland and the Netherlands is ALWAYS 1 hour, i.e. the Netherlands is always 1 hour ahead of Ireland.
How do you explain that the time jumped 2 hours back after the Power BI Service refresh??
Very nice tutorial and learned something new. It would be nice if PowerBI had a function to do it, but I liked your approach.
Agreed, I too wish they also had a built-in approach. So far this is the best alternative I've found
What happens for users in different timezones? This will only work for the offset you statically set.
Is it possible time zone offset to be detected from the user browser and time refresh to be correctly shown to users in different time zones?
Hey aleksander,
With the video example it only works statically. If you want a dynamic example based on the user location, I imagine you could build a solution in DAX that uses row level security.
The row level security table could then use offset values based on the location of your user.
I may make a video on this at some point :)
@@BIGorilla thanks, that could work. For public reports (publish to web) probably we need to look for another workaround since detecting web browser data is not an option.
btw great videos, keep up with the great work.
@@aleksandarboshevski I haven' t found a good solution for that one. There are solutions out there that call to an API to return the correct daylight savings time. However, that one is also bound to the location where your report is refreshed. And with that it does not solve your challenge.
Dear Rick, great approach, I've been looking for it for a very long time. I have a similar issue with decimal comma in desktop but decimal point in service. In addition thousand seperator point is changed to comma as well. Do you know if there is a solution for that as well ? Your answer will be highly appriciated. Have a nice day.
Hi! yes that's frustrating. To change the decimal separator, a user should login to the power bi service , go to settings, look for Display language and change their language settings in the browser. It's a personal setting that you can set.
Dear Rick, thanks a lot for your answer, I appriciate it very much !@@BIGorilla
Excellent video sir, But one question here, Pls clarify me
I have UTC datetime column which contains dates between 2020 to 2030 ( let's assume ), I have to create one custom column which is offset column for both ( Daylight Saving Time = UTC - 5 && Central Standard Time = UTC - 6 ). How to create that offset column based on UTC timestamp column.
OR How to create directly a custom column by converting UTC timestamp column to CST and CDT timestamp
Same as in the video. Except change the
DateTimeZone.UtcNow()
To your DateTimeZone Value in your column. You can take a look at my written post if you like to copy paste it gorilla.bi/power-query/last-refresh-datetime/
Enjoy!
@@BIGorilla But the daylight saving time dates are different for each year right? ( for Year2022 ---> 13.March.2022=
@@BIGorilla correct me if i am wrong. Daylight Saving Time ( Also called Central Daylight Time ) starts every year at 2nd Sunday of March at=2 AM and ends before 1st sunday of November 2 AM.
@@kummithavenkatareddy2302 The example shown uses mostly the dates. You can easily add the time of 2 AM to it.
The logic is that wintertime starts the last sunday in October. And summertime the last sunday of march.
The code for that is in my blog. You only have to adjust it to add the 2AM. I'm sure you'll manage 🙌🏻🙌🏻
It doesn't work, Your time before refresh was 22:00 hours, after refresh in service you still have 10 pm. I don't think I'm mistaken. But none of my attempts at this resulted in the change. I followed the steps to a T. Has anyone else experience this?
Hi! Your observation is correct, but the conclusion is not. Here's what happened. I uploaded a file with 22:00 hours. When I refreshed the file in the power bi Service, the time swapped to 20:00 hours because the server refreshes in a different timezone.
Using the code I provided, you can make sure to always get right time, regardless of the location of the refresh. Hope that clarifies. If it's still unclear, please watch the video again. It is explained in there.
Thanks,
Rick