Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to add hours to datatime based on a condition in Power Query?

Hi,   I need some help in adding a new DateTime column based on a condition on an existing DateTime column.   Requirement:     Thank you, Brijesh
Need Help
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi Anonymous 

 

Try this DAX to change the output to the Date, and use that for relationship (make sure the format is Date for that column)

Final Output =
VAR _Time =
    TIMEVALUE ( 'Table'[Date Time] )
VAR _Date =
    DATEVALUE ( 'Table'[Date Time] )
VAR _5_PM =
    TIME ( 17, 00, 00 )
VAR _9_AM =
    TIME ( 9, 0, 0 )
Var _Out = 
    IF ( _Time > _5_PM, _Date + 1 + _9_AM, 'Table'[Date Time] )
return
    DATEVALUE ( _Out )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi VahidDM 

Thank you again for the effort.

This looks good. However, one thing I have noticed with this solution is that if a 'start_date_time' is on Friday after 5 PM then it is returning Saturday's date with 9 AM time! Is it possible if it checks for the weekend and return the following Monday date? 

Thank you

Brijesh