Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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 )
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.
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] )
This is the error I am getting when I don't use the date portion in the calculated column:
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?
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
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 )
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |