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,

     

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

hello javirmerino 

 

I'm a little confused where exactly you want to have the solution. If you are still going for the Power Query option try to change your code as follows:

if  Value.Compare([ReceivedTime], #time(17,0,0)) = 1
 then DateTime.From([NextWorkingDay])+Duration.FromText(Text.From([StartTime] )) 
else DateTime.From([ReceivedDate] )+Duration.FromText(Text.From([StartTime] ))


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

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