Selecting First Arrival date and time for a dispatch ticket that is not within the year 1899

[Anonymous]

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.

[jdbuchanan71]

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
)

[Anonymous]

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

[Anonymous]

This is the error I am getting when I don't use the date portion in the calculated column:

[jdbuchanan71]

Odd, I wonder if it from using the hidden columns.

Try this.

First Arrival Time (C) =
CALCULATE (
    MIN ( SV_Service_Ticket_Dispatch[Arrival_Time] ),
    YEAR ( SV_Service_Ticket_Dispatch[Arrival_Time] ) > 1899
)

If that doesn't work, can you share your .pbix?

[Anonymous]

That did not work either. I got the following error:

The .pbx file I am using can be located in the following DropBox link:

https://www.dropbox.com/s/p87k64mrnuison1/SERVICE%20TICKET%20DATA.pbix?dl=0 

[jdbuchanan71 · Accepted solution]

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
)

[Anonymous]

It is working like a charm, for both column and measure! I really appreciate you taking time to help me out with this issue. I will accept your response as the solution to this issue.