Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

 

Service  Ticket ID 110025 SQL Table.jpg

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.

 

Service  Ticket ID 110025 Power BI Result.jpg

 

Is there something I many be missing here? Your help with this request/issue will be greatly appreicated.

 

 

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

FirstArrivalTime.jpg

 

 

View solution in original post

8 REPLIES 8
jdbuchanan71
Super User
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
)
Anonymous
Not applicable

Hello  @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
Not applicable

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

 

Power BI MIN formula error.jpg

 

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
Not applicable

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

Power BI MIN formula error II.jpg

 

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 

 

 

 

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
)

FirstArrivalTime.jpg

 

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.