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,

     

artemus's avatar
artemus
Microsoft Employee
6 years ago

Change the column type to DateTime to fix this.

javirmerino's avatar
javirmerino
Helper III
6 years ago

Hi artemus, thanks for your response. I cringed when i saw it as i thought "it cant be that simple, surely?!" - and fortunately for me, it wasn't! 

 

I'm unable to view or format the [NextWorkingDay] column in QueryEditor as its a DAX measure so i'm assuming the only way to amend the formatting of the output is via the query itself?

 

Or could the query be converted into M so its added as a calculated column instead?

NextWorkingDay = 
minx( 
    FILTER(
        ALL(Dim_Date), Dim_Date[IsWeekday] = TRUE  && 
        Dim_Date[IsHolidayUK] = FALSE && 
        Dim_Date[Date] > SELECTEDVALUE('SysAid Extract'[ReceivedDate]) 
    ),Dim_Date[Date])