Forum Discussion

javirmerino's avatar
javirmerino
Helper III
6 years ago
Solved

Combining calculated Date & Time Fields

Hi guys, i'm trying to use the below query to determine a value based on a number of fields; if Value.Compare([ReceivedTime], #time(17,0,0)) = 1 then [NextWorkingDay] + [StartTime] else [Recei...
Need Help
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi javirmerino ,

     

    We can create a custom column using following query to meet your reqirement:

     

    if  Value.Compare([ReceivedTime], #time(17,0,0)) = 1
 then DateTime.From([NextWorkingDay])+Duration.FromText(Time.ToText([StartTime],"hh:mm:ss"))
else DateTime.From([ReceivedDate] )+Duration.FromText(Time.ToText(Text.From([StartTime],"hh:mm:ss"))

     

    Or 

     

    if  Value.Compare([ReceivedTime], #time(17,0,0)) = 1
 then DateTime.From(Date.ToText([NextWorkingDay]) & Time.ToText([StartTime]," hh:mm:ss"))
else DateTime.From(Date.ToText([ReceivedDate]) & Time.ToText([StartTime]," hh:mm:ss"))

     


    Best regards,

     

javirmerino's avatar
javirmerino
Helper III
6 years ago

Thanks Jimmy801. Please excuse me if i confuse you at all - i've only been using PowerBI for around a month (Tableau convert, here) so i'm unsure of the best practices and when to use M or DAX. Any guidelines or rules of thumb would be appreciated here.

 

I will give the code a go shortly and will mark as the answer if this works out.

 

Thanks again!

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi javirmerino ,

 

We can create a custom column using following query to meet your reqirement:

 

if  Value.Compare([ReceivedTime], #time(17,0,0)) = 1
 then DateTime.From([NextWorkingDay])+Duration.FromText(Time.ToText([StartTime],"hh:mm:ss"))
else DateTime.From([ReceivedDate] )+Duration.FromText(Time.ToText(Text.From([StartTime],"hh:mm:ss"))

 

Or 

 

if  Value.Compare([ReceivedTime], #time(17,0,0)) = 1
 then DateTime.From(Date.ToText([NextWorkingDay]) & Time.ToText([StartTime]," hh:mm:ss"))
else DateTime.From(Date.ToText([ReceivedDate]) & Time.ToText([StartTime]," hh:mm:ss"))

 


Best regards,