Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
Thanks in advance for your help!
I'm working on a report that I want to see when an action is done throughout my company where we have locations in each time zone. Our current system uses UTC.
I've located a measure which works, however once I add a new column the adjusted column goes blank. I tried a different way by store number, however this only gives me the value for if it's not actually changing, but gives me "12/30/1899 3:00:00 AM" for Pacific time as an example. This way would be the most ideal due to some states having locations in 2 different time zones.
Along with this, I can't pull just the time out of the value to allow me to filter if the action is done after a specified time.
Wondering if anyone knows of a way to help get this to work?
Attempt 1 - Hour Conversion, converts the state to the hour behind EST time (works but dissapears):
"New DateTime =
VAR UTCDateTime = SELECTEDVALUE(OPS_PULL_LIST_DATA[FIRST_ACTION_TAKEN_DTTM])
VAR TimeZone = SELECTEDVALUE(OPS_PULL_LIST_DATA[STATE_ABBR])
VAR UTCOffset = SWITCH(TimeZone, "CA", 3, "WA", 3, "NV", 3, "AZ", 2, "NM", 2, "CO", 2, "MN", 2, "WI", 2, "IL", 1, "MO", 1, "KS", 1, "TX", 1, "LA", 1, "MA",0, "NY", 0, "CT", 0, "MD", 0, "NJ", 0, "DE", 0, "NC", 0, "VA", 0, "GA", 0, "SC", 0, "FL", 0, "TN", 0, "KY", 0, "IN", 0, -1)
RETURN
SWITCH(
TRUE(),
UTCOffset > -1, UTCDateTime - TIME(UTCOffset,0,0),
UTCDateTime)"
Attempt 2 -
Hour Conversion measure is built off of adjusting the store number to the number of hours behind the time zone is from EST:
New DateTime measure (works for only non adjusted times):
Attempted to pull out the Time based off of the New DateTime measure, but use the original data if the New DateTime was blank, but this didn't work:
When a new column is added:
Thanks again for your all's help!
Hey @Idrissshatila thanks for the message. Unfortunetly with the data I'm using, I do not have access to edit the dataset. I tried doing this through the Report View and couldn't get it to work.
Do you know of any way to do something similar without updating the dataset? Turnaround time is not quick to get those in charge to do it on their end.
Thanks!
one of the most popular topics
Solving DAX Time Zone Issue in Power BI - RADACAD
In all fairness - it is very strange that Power Query has native timezone support but DAX doesn't.
HEllo @ccolletti ,
I see, then check this https://youtu.be/W433EP-4eoc?si=7-WANWvm6K0PfR7s
or
this https://youtu.be/fRZkzuyGZM0?si=vsgphtyAZBVPL660
Proud to be a Super User! | |
Hello @ccolletti ,
check if this helps https://youtu.be/Goysa4jw7Ms?si=JBffl9ixNoFLG5Tl
Proud to be a Super User! | |
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |