Implementing Daylight Savings in Power BI

[avikkal89]

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. 

[parry2k]

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]

[avikkal89]

@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. 

[v-haibl-msft]

@avikkal89

Could you please provide the expected results for PST column just like the Table screenshot you uploaded in the first post?

Best Regards,
Herbert