Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

TimeZone PQ to Calculated Column

Hi good day,

Can someone help me on how can i convert my PQ Timezone custom column to calculated column.

 

"Today", each if [PlanDate] < Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0))) then 0 else [PlanDate])

 

Thank you

Need Help
  • Prasad_chattu's avatar
    Prasad_chattu
    1 year ago

    Hello AllanBerces 

    Please try the below DAX column :


    Today =
    VAR AdjustedDate = DATE(YEAR(UTCNOW()), MONTH(UTCNOW()), DAY(UTCNOW())) + (8/24)
    RETURN IF( [PlanDate] < AdjustedDate, 0, [PlanDate])

    I have tried with sample data :


    Let me know if it works or not.

    Thank you.




  • danextian's avatar
    danextian
    1 year ago

    Hi AllanBerces 

     

    Please try this:

    test = 
IF (
   // Convert the current UTC date-time to an integer (removing the time component)
   // 'UTCNOW()' returns the current UTC timestamp.
   // 'DIVIDE(8,24)' converts 8 hours into a fraction of a day (since 1 day = 24 hours).
   // Adding this fraction to 'UTCNOW()' adjusts the time to UTC+8.
   // 'INT(...)' ensures we only compare the **date part**, discarding the time.
   'Table'[Plan Date] < INT( UTCNOW() + DIVIDE(8,24)), 
    
    // If 'Plan Date' is earlier than the adjusted current date, return 0.
    0,  
    
    // Otherwise, return the original 'Plan Date'.
    'Table'[Plan Date]  
)

3 Replies

  • Prasad_chattu's avatar
    Prasad_chattu
    Regular Visitor
    1 year ago

    Hello AllanBerces 

    Please try the below DAX column :


    Today =
    VAR AdjustedDate = DATE(YEAR(UTCNOW()), MONTH(UTCNOW()), DAY(UTCNOW())) + (8/24)
    RETURN IF( [PlanDate] < AdjustedDate, 0, [PlanDate])

    I have tried with sample data :


    Let me know if it works or not.

    Thank you.




  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi AllanBerces 

     

    Please try this:

    test = 
IF (
   // Convert the current UTC date-time to an integer (removing the time component)
   // 'UTCNOW()' returns the current UTC timestamp.
   // 'DIVIDE(8,24)' converts 8 hours into a fraction of a day (since 1 day = 24 hours).
   // Adding this fraction to 'UTCNOW()' adjusts the time to UTC+8.
   // 'INT(...)' ensures we only compare the **date part**, discarding the time.
   'Table'[Plan Date] < INT( UTCNOW() + DIVIDE(8,24)), 
    
    // If 'Plan Date' is earlier than the adjusted current date, return 0.
    0,  
    
    // Otherwise, return the original 'Plan Date'.
    'Table'[Plan Date]  
)