I have a column of date/time. I expected PowerBI to change it based on UTC, which I know how to edit/account for. Except what I'm finding now is that some dates are changing by 4 hours and others are changing by 5. I cannot find a pattern as to what ones are following what rule and I cant figure out why theyre changing inconsistently.
I want to change all the date/times to match my original data but I cant when they arent changing by the same amount of hours.
The Date/Time Format from my original data is Column A.
Column B-D is what is coming out of PowerBI.
Column E is showing 2 of them are off by 4 hours and the other two are off by 5 hours.
My data in reality is thousands of lines long and will be updated weekly with more data, so I need to write a new column that will account for all possible situations with the Date/Time but just a simple 4 or 5 wont work since its not consistently changing.
Any ideas as to whats happening and how to fix it?
CodePudding user response：
Your question is incomplete in 2 regards:
What do you mean with PowerBI? You can do conversions in both M and DAX, but only M has a DateTimeZone data type, DAX (like Excel) hasn't.
What timezone is your original data? M can do basic timezone conversion, but it doesn't consider DST.
From looking at your sample data the pattern is: During summertime you have 4 hours difference, during wintertime it's 5 hours.