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.
Hi all
Struggling with some datetime conversions in PowerBI Desktop and Service. Seen this topic all over the internet with no great answer. I'm hoping someone has a simple function I can invoke to solve this, as I have many datetime columns to convert.
I am pulling datetimes from a number of sources, which are mostly in UTC. In PowerBI Desktop I'm using ToLocal to display these in EST, which is my local time, and is how my end users need to see the data.
However this does not work in the report after publishing to the PowerBI service, which seems to use UTC as its local time.
If I take 18:00 UTC, ToLocal has no effect on the data -- it is still shown as 18:00 in the PowerBI Service.
A simple solution would just be to subtract X hours from my UTC times in PowerQuery. The data would "looks" like EST in PowerBI Desktop, and will "look" like EST in the PowerBI service (even though PowerBI thinks it is a UTC time).
However this does not account for daylight savings at all. It doesn't help of course that DST start/end changes every year.
So I'd like an M function that can...
-> Read in a DateTime that is in UTC
-> Check if that DateTime falls into Daylight Savings
-> If yes, subtract 5 hours
-> If no, subtract 4 hours
Any ideas?? Surely this has been solved somewhere!
Solved! Go to Solution.
Hi @Anonymous ,
check the post below from RADACAD they present a couple of options to make this changes.
https://radacad.com/solving-dax-time-zone-issue-in-power-bi
Maybe one can fit your needs.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Just do like this.
// Modify the parameters of the TIME() function according to the time difference between the two time zones
Measure = UTCNOW() + TIME(8, 0, 0)
For example, my current time differs from UTC time by 8 hours, so I add UTC time to 8 hours to get my current time.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Just do like this.
// Modify the parameters of the TIME() function according to the time difference between the two time zones
Measure = UTCNOW() + TIME(8, 0, 0)
For example, my current time differs from UTC time by 8 hours, so I add UTC time to 8 hours to get my current time.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
check the post below from RADACAD they present a couple of options to make this changes.
https://radacad.com/solving-dax-time-zone-issue-in-power-bi
Maybe one can fit your needs.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |