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 zone format, using rules of first table. Please advice how to do this in PQ? I tried creating  List of all dates/times (yyyy/mm/dd hh:mm:ss AM/PM format) within ranges (rules table), and then do the merge, but such method takes too long. Thanks

Tables 

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 )

     

     

1 Reply

  • 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 )