Help with DAX Measure - Dock availability for ships at different docks.

[mcc]

I am trying to see how many hours and minutes are from the departure of one vessel to the arrival of the next.  I wrote the code, but having issue with the hours over 24.  See 2 examples below:

Dock Availability formula is shown in the Availability column.  

Dock Availability =

VAR CurrentVesselDeparture = MIN(Vessels[DepartureTime])

VAR CurrentDock = SELECTEDVALUE(Vessels[Dock])

VAR NextVesselArrival =

    CALCULATE(

        MIN(Vessels[ArrivalTime]),

        FILTER(

            ALL(Vessels),

            Vessels[Dock] = CurrentDock &&

            Vessels[DepartureTime] > CurrentVesselDeparture

        )

    )

RETURN

    IF(

        ISBLANK(NextVesselArrival),

        BLANK(),

        NextVesselArrival - CurrentVesselDeparture

        )

Option 2

Dock Duration2 =

VAR CurrentFlightDeparture = MIN(Vessels[DepartureTime])

VAR CurrentGate = SELECTEDVALUE(Vessels[Dock])

VAR NextFlightArrival =

    CALCULATE(

        MIN(Vessels[ArrivalTime]),

        FILTER(

            ALL(Vessels),

            Vessels[Dock] = CurrentGate &&

            Vessels[DepartureTime] > CurrentFlightDeparture

        )

    )

VAR Durationseconds =

    IF(

        ISBLANK(NextFlightArrival),

        BLANK(),

        NextFlightArrival - CurrentFlightDeparture

    )

RETURN

    IF(

        ISBLANK(Durationseconds),

        BLANK(),

        FORMAT(Durationseconds / (60 * 60* 24),"[hh]:mm:ss")

    )

This result is in the Dock Duration colum of the Dock Hours visual.  

Suggestions?

[speedramps]

@mcc 

Please dont be lazy next time. You can share test data. Just copy and paste it into excel or a PBIX and hide any private data .

It is unfair to expect solvers to have to input your test data for you.

Remember we are unpaid voluntreers helping you. Thank you.

Try this solution ...

Click here to download PBIX from Onedrive 

How it works ..

Copy and paste your test data

Create a measure to get minutes.

This is held a "number" data type so can handle values over 24 hrs

Minutes docked = 
DATEDIFF(
    SELECTEDVALUE('Table'[Arrived]),
    SELECTEDVALUE('Table'[Departed]),
    MINUTE)

Create a measure to display the number of minutes as string

HH:MM docked = 
-- convers decimal duration to hh:mm:ss text   eg converts  5.5 to  "00:05:30"   and converts 0 to  00:00

-- Take decimal minutes as input eg 5.5 minutes
VAR DecimalMinutes = [Minutes docked]
-- Convert decimal minutes to seconds
VAR Duration = DecimalMinutes * 60
-- Calc the days, hours, minutes and seconds  (add days if required)
VAR Hours = INT(DIVIDE(Duration,3600,0))
VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
-- Format hours, minutes and seconds to two decimals
VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN
-- if input is 0 return 00:00.
-- return hours:mins:seconds as text
-- handles any errors(unlikely)
-- if inpout is greater than 24hrs then the hours will be displayed
IFERROR(
    IF (DecimalMinutes>0, 
    H & ":" & M & ":" & S,
    "0:00"),
    "Error")

This solution works and does exactly what you asked.

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort.
So please be polite and quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[123abc]

It seems like your DAX measures are generally on the right track, but you're encountering issues when the duration exceeds 24 hours. This is because you're converting the duration to a time format directly without considering days.

To handle durations exceeding 24 hours properly, you need to calculate the number of days separately and then format the remaining time accordingly. Here's how you can modify your measures to achieve this:

Dock Availability =
VAR CurrentVesselDeparture = MIN(Vessels[DepartureTime])
VAR CurrentDock = SELECTEDVALUE(Vessels[Dock])

VAR NextVesselArrival =
CALCULATE(
MIN(Vessels[ArrivalTime]),
FILTER(
ALL(Vessels),
Vessels[Dock] = CurrentDock &&
Vessels[DepartureTime] > CurrentVesselDeparture
)
)

VAR DurationSeconds =
IF(
ISBLANK(NextVesselArrival),
BLANK(),
NextVesselArrival - CurrentVesselDeparture
)

RETURN
IF(
ISBLANK(DurationSeconds),
BLANK(),
FORMAT(DurationSeconds, "[d].hh:mm:ss")
)

Option 2: Dock Duration

Dock Duration2 =
VAR CurrentFlightDeparture = MIN(Vessels[DepartureTime])
VAR CurrentGate = SELECTEDVALUE(Vessels[Dock])

VAR NextFlightArrival =
CALCULATE(
MIN(Vessels[ArrivalTime]),
FILTER(
ALL(Vessels),
Vessels[Dock] = CurrentGate &&
Vessels[DepartureTime] > CurrentFlightDeparture
)
)

VAR DurationSeconds =
IF(
ISBLANK(NextFlightArrival),
BLANK(),
NextFlightArrival - CurrentFlightDeparture
)

RETURN
IF(
ISBLANK(DurationSeconds),
BLANK(),
FORMAT(DurationSeconds, "[d].hh:mm:ss")
)

In these modifications, I used the FORMAT function to format the duration with days, hours, minutes, and seconds. [d] represents days, hh represents hours, mm represents minutes, and ss represents seconds.

This way, if the duration exceeds 24 hours, it will correctly display the number of days along with hours, minutes, and seconds. Make sure your data types for DepartureTime and ArrivalTime are in DateTime format for these calculations to work accurately.

[speedramps]

Variables with time data type will only store 24hrs.

So if you add 13+13 you get 2hrs and not 26hr.

It is best practice to convert to secons and change the data type to numeric.

We want to help you but your description is too vaugue. Please write it again clearly.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀

Please just give a simple non technical functional decscription of what you want, then let us suggest the solution. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confussion.

Rename columns to user friendly names.
Also provide the example desired output, with a clear description of the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gerrting free expert help, so please put lots of proper effort to asking questions and providing example.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

[mcc]

Thank you.

I cannot share the BI as it is interfaced to our data warehouse. Maybe it would be easier to say this:  I have a ship that arrived at Dock 5 on  14-Dec-23 @ 02:00 it left on 17-Dec-23 @ 14:32.  The total time the ship was at Dock 5 is 84:32.  I created a measure to calculate this, becasue I have specific filters in the calculation.  The measure works perfectly as long as the ship is at the dock under 23:59 hours.  Once it exceeds 24 hours, it does not work.  How do I turn a measure with a variable calculation into duration?