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
palnarum
Regular Visitor

undefined

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_LOCALDATE/TIMEREPORT_TYPEVOYNUMPOSITIONPosition_Custom
14.03.2024 22:0014.03.2024 22:00Arrival132Arr. JEBEL ALIJEBEL ALI
17.03.2024 04:4217.03.2024 04:42Arrival132Arr. ABU DHABIABU DHABI
21.03.2024 09:0021.03.2024 09:00Arrival132Arr. MUMBAIMUMBAI
29.03.2024 08:0029.03.2024 08:00Arrival132Arr. SINGAPORESINGAPORE
16.03.2024 23:5416.03.2024 23:54Departure132Dep. JEBEL ALIJEBEL ALI
18.03.2024 05:3018.03.2024 05:30Departure132Dep. ABU DHABIABU DHABI
22.03.2024 17:3022.03.2024 17:30Departure132Dep. MUMBAIMUMBAI
30.03.2024 10:4830.03.2024 10:48Departure132Dep. SINGAPORESINGAPORE

 

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!

2 REPLIES 2
v-kaiyue-msft
Community Support
Community Support

Hi @palnarum ,

 

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

)

 

vkaiyuemsft_0-1716514455734.png

 

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.