Forum Discussion
Selecting First Arrival date and time for a dispatch ticket that is not within the year 1899
Hello community,
I have the following table in SQL server that shows data for ticket ID 110025:
I am using the following calculated column formula to extract the first arrival date and time that is not in the year 1899:
First Arrival Time (C) = CALCULATE(FIRSTDATE(SV_Service_Ticket_Dispatch[Arrival_Time].[Date]),SV_Service_Ticket_Dispatch[Arrival_Time].[year]>1899)
The problem I am having is that this calculated column formula returns the first date but seems to be ignoring the time data in the Arrival_Time column. It returns the correct day but sets the time to 12:00:00.
Is there something I many be missing here? Your help with this request/issue will be greatly appreicated.
I didn't know you were trying to add it as a column.
First Arrival Time (C) = VAR Service = SV_Service_Ticket_Dispatch[Service_Ticket_Id] RETURN CALCULATE ( MIN ( SV_Service_Ticket_Dispatch[Arrival_Time] ), ALL ( SV_Service_Ticket_Dispatch ), SV_Service_Ticket_Dispatch[Service_Ticket_Id] = Service, YEAR ( SV_Service_Ticket_Dispatch[Arrival_Time] ) > 1899 )As a measure it is like this
First Arrival Time (M) = CALCULATE( MIN ( SV_Service_Ticket_Dispatch[Arrival_Time] ), YEAR (SV_Service_Ticket_Dispatch[Arrival_Time] ) > 1899 )
8 Replies
- jdbuchanan71
Super User
Hello Anonymous
Instead of FIRSTDATE try using MIN.
First Arrival Time (C) = CALCULATE ( MIN ( SV_Service_Ticket_Dispatch[Arrival_Time] ), SV_Service_Ticket_Dispatch[Arrival_Time].[year] > 1899 )Hello jdbuchanan71 ,
Thank you for your help. I tried using the MIN function, but still get the same result. The time element is coming across as 12:00:00 AM.
- jdbuchanan71
Are you using just
MIN ( SV_Service_Ticket_Dispatch[Arrival_Time] )
You can't include the date protion of the column or it will return only the date and ignore the time.
This won't work:
MIN ( SV_Service_Ticket_Dispatch[Arrival_Time].[Date] )