Add and/Subtract Hours from DATETIME and/or Filter

[reportuser]

I need to be able to add hours so that it can reflect timezones and filter. E.g.

DATETIME Column

Case 1:
03/22/2021 3:25:49 PM

- Case 1 is in EST

- I need to have a filter which can select between EDT, CDT, MDT, and PDT

IF 'EDT' THEN DATETIME = 03/22/2021 3:25:49 PM

IF 'CDT' THEN DATETIME = 03/22/2021 2:25:49 PM

IF 'MDT' THEN DATETIME = 03/22/2021 1:25:49 PM

IF 'PDT' THEN DATETIME = 03/22/2021 12:25:49 PM

[v-jingzhang · Accepted solution]

Hi @reportuser 

You could store all time zone names in a column of a table, and use this column in a filter or slicer for users to switch the time zone. Then create a measure like below to get the date time according to the time zone. The offset hours I used are different from yours. You can modify that per your need.

New DateTime = 
VAR UTCDateTime = SELECTEDVALUE ( 'Table'[DATETIME] ) + TIME ( 5, 0, 0 )
VAR TimeZone = SELECTEDVALUE ( 'Time Zone'[TimeZone] )
VAR UTCOffset = SWITCH ( TimeZone, "EST", -5, "EDT", -4, "CDT", -5, "MDT", -6, "PDT", -7, 0 )
RETURN
    SWITCH (
        TRUE (),
        UTCOffset >= 0, UTCDateTime + TIME ( UTCOffset, 0, 0 ),
        UTCOffset < 0, UTCDateTime - TIME ( ABS ( UTCOffset ), 0, 0 )
    )

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.