undefined

[Anonymous]

Hi there! I need some assistance with calculating a time difference between values in the same column. I have seen some other posts on the same topic, but they are not similar enough to my issue that i have been able to use them.

I have this extract from a large dataset

EVENTDATE_LOCAL	DATE/TIME	REPORT_TYPE	VOYNUM	POSITION	Position_Custom
14.03.2024 22:00	14.03.2024 22:00	Arrival	132	Arr. JEBEL ALI	JEBEL ALI
17.03.2024 04:42	17.03.2024 04:42	Arrival	132	Arr. ABU DHABI	ABU DHABI
21.03.2024 09:00	21.03.2024 09:00	Arrival	132	Arr. MUMBAI	MUMBAI
29.03.2024 08:00	29.03.2024 08:00	Arrival	132	Arr. SINGAPORE	SINGAPORE
16.03.2024 23:54	16.03.2024 23:54	Departure	132	Dep. JEBEL ALI	JEBEL ALI
18.03.2024 05:30	18.03.2024 05:30	Departure	132	Dep. ABU DHABI	ABU DHABI
22.03.2024 17:30	22.03.2024 17:30	Departure	132	Dep. MUMBAI	MUMBAI
30.03.2024 10:48	30.03.2024 10:48	Departure	132	Dep. SINGAPORE	SINGAPORE

I would like to create a new column that calculates the time between Arrival and Departure if the VOYNUM and Position_Custom is the same. Would be great if anyone has any ideas!

[Anonymous]

Hi @Anonymous ,

Create calculate column.

Time Difference =

VAR CurrentRowDate = 'Table'[EVENTDATE_LOCAL]

VAR CurrentVOYNUM = 'Table'[VOYNUM]

VAR CurrentPosition = 'Table'[Position_Custom]

VAR DepartureDate =

    CALCULATE(

        MIN('Table'[EVENTDATE_LOCAL]),

        FILTER(

            'Table',

            'Table'[VOYNUM] = CurrentVOYNUM

            && 'Table'[Position_Custom] = CurrentPosition

            && 'Table'[REPORT_TYPE] = "Departure"

            && 'Table'[EVENTDATE_LOCAL] > CurrentRowDate

        )

    )

RETURN

IF('Table'[REPORT_TYPE] = "Arrival" && NOT(ISBLANK(DepartureDate)),

    DATEDIFF(CurrentRowDate,DepartureDate,HOUR) / 24,

    BLANK()

)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you Clara

Unfortunately, I have a DirectQuery model and get the error message that the function CALCULATE is not allowed. I also tried to use the formula as a measure but it seems the issue is then that a single value for column 'EVENTDATE_LOCAL' in 'Table' cannot be determined. Not quite sure how to proceed.