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 Community,
I have a scenario where the date column is in UTC. We have a DST flag which changes on 12am of second Sunday of March and first Sunday of November. (Currently the business scenario is that the DST flag changes at 12am and not 2am)
As you see, the DST flag changes to "1" at 3/11/1900 12:00:00.
I have a PST column with the following DAX
PST = IF(Sheet1[DST_Flag] = 0, 'Sheet1'[Date]-8/24, Sheet1[Date]-7/24) (ie; subtracts 8 hours when DST_flag= 0, else subtracts 7 hours)
Now, I would like to implement a logic (in DAX) where the PST time gets subtracted to -7 only when the PST time is 3/11/1900 12:00:00 and NOT when the UTC time is 12am.
not clear what your question is, you have utc and you are calculating pst based on flag, are you saying you want to further substract 7 hours from pst if date is 3/11/1900
also your formula shows the colur name [Date] where as in your table image it is called [Date UTC]
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k
[Date UTC] and [Date] are the same columns
The DST flag is currently implemented for UTC timings. However I am trying to get the equivalent PST for the respective UTC.
For example,
when the UTC date and time is - 03/10/1900 11PM, the equivalent PST is 3/10/1900 3pm (Subtracting 8 hours)
DST flag changes from 0 to 1t UTC timing 03/11/1900 12AM, at this point the PST shouldn't be 3/10/1900 5PM. The result we need here is 3/10/1900 4PM.
Even though the DST flag gets into effect when UTC time changes to 12am, the equivalent time at PST is still in the previous day. This is the logic I am fail to implement.
Kindly let me know if this was clear enough.
Could you please provide the expected results for PST column just like the Table screenshot you uploaded in the first post?
Best Regards,
Herbert
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |