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

i've resolved one issue by changing the +'s to &'s (which worked lovely!) but now its caused a secondary issue which i believe is something to do with my Next Working Day measure, as any values using this are returning an error;

Expression.Error: We cannot apply field access to the type Text.
Details:
    Value=Σ Measures
    Key=NextWorkingDay

 

Would this be anything to do with one being a measure written in DAX and one being a query written in #M? If so, do i just need to rewrite my [NextWorkingDay] measure in PowerQuery?

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])