Search code examples
powerbidaxmeasure

Calculate time difference in same column based on couple filters PowerBI

Good day,

I'm looking for a measure that will calculate the time difference based on certain filters. The measure I'm looking is for example, I would like to calculate the time needed for a vessel port call (unique "number") column from "Fieldname" = "Pilot On Board inwards" to "Fieldname" = "All Fast" in hours. I would like to have the first Pilot on boards inwards to first All Fast.

I can the reuse the same measure to calculate other duration for the same port call.

Example

Example data

Number Port Ship Trip FieldName FromDate ToDate
1 Antwerpen S 22017 End of sea passage 18/09/2022 15:45
1 Antwerpen S 22017 Anchored 18/09/2022 16:30
1 Antwerpen S 22017 NOR tendered 18/09/2022 16:30
1 Antwerpen S 22017 Pilot on board inwards 19/09/2022 03:55
1 Antwerpen S 22017 Anchor aweigh 21/09/2022 02:40
1 Antwerpen S 22017 First line ashore 21/09/2022 13:20
1 Antwerpen S 22017 All fast 21/09/2022 13:30
1 Antwerpen S 22017 Tanks accepted/passed 21/09/2022 14:15
1 Antwerpen S 22017 NOR accepted 21/09/2022 14:25
1 Antwerpen S 22017 Hose connected 21/09/2022 17:00
1 Antwerpen S 22017 Cargo loading 21/09/2022 17:15 22/09/2022 00:36
1 Antwerpen S 22017 Hose disconnected 22/09/2022 02:40
1 Antwerpen S 22017 Hose connected 22/09/2022 02:50
1 Antwerpen S 22017 Cargo loading 22/09/2022 03:47 22/09/2022 14:48
1 Antwerpen S 22017 Cargo loading 22/09/2022 15:42 22/09/2022 17:30
1 Antwerpen S 22017 Hose disconnected 22/09/2022 20:36
1 Antwerpen S 22017 Cargo docs on board 22/09/2022 21:48
1 Antwerpen S 22017 Left berth 23/09/2022 09:12
1 Antwerpen S 22017 Pilot on board
1 Antwerpen S 22017 Surveyor on board

Kind regards,

Solution

  • The following calculated column should work.

    enter image description here

    Column = 
IF(
    'Table'[FieldName] = "All fast",
    VAR d = CALCULATE(MIN('Table'[FromDate]), ALLEXCEPT('Table',  'Table'[Number]), 'Table'[FieldName] = "Pilot on board inwards")
    RETURN 
        DATEDIFF( d,'Table'[FromDate], HOUR)
)

    EDIT New measure:

    Column = 
IF(
    'Table'[FieldName] = "All fast"  && CALCULATE(MIN('Table'[FromDate]),ALLEXCEPT('Table',  'Table'[Number]), 'Table'[FieldName] = "All fast") = 'Table'[FromDate] ,
    VAR d = CALCULATE(MIN('Table'[FromDate]), ALLEXCEPT('Table',  'Table'[Number]), 'Table'[FieldName] = "Pilot on board inwards")
    RETURN 
        DATEDIFF( d,'Table'[FromDate], HOUR)
)