Forum Discussion

tomislav's avatar
tomislav
Frequent Visitor
7 years ago
Solved

Lookup based on start and end date/time

HI,  I have one table containing rules for conversion of local date/time to UTC time zone format. What I want to do is to convert local times in second table (from known time zone) into UTC time zon...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi tomislav ,

     

    We can achieve that by DAX.

    Column = 
VAR a =
    - CALCULATE (
        MAX ( RULES[OffsetFromUTC_ForLocalTime] ),
        FILTER (
            RULES,
            RULES[EndOfTimeZoneOffset] >= 'TABLE TO CONVERT'[CreatedDate]
                && RULES[StartOfTimeZoneOffset] <= 'TABLE TO CONVERT'[CreatedDate]
        )
    )
RETURN
    'TABLE TO CONVERT'[CreatedDate] + TIME ( a, 0, 0 )

     

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi tomislav ,

 

We can achieve that by DAX.

Column = 
VAR a =
    - CALCULATE (
        MAX ( RULES[OffsetFromUTC_ForLocalTime] ),
        FILTER (
            RULES,
            RULES[EndOfTimeZoneOffset] >= 'TABLE TO CONVERT'[CreatedDate]
                && RULES[StartOfTimeZoneOffset] <= 'TABLE TO CONVERT'[CreatedDate]
        )
    )
RETURN
    'TABLE TO CONVERT'[CreatedDate] + TIME ( a, 0, 0 )