Voyage duration - DAX Expression

[SofiaVaz]

Hi all,

It's been a while since I've last use PB (and I was never an expert on this) so I need your help for what I think might be a simple measure.

Imagine a db with voy number, vessel name, VoyNav (voy no and vessel combined, witch is like an voyage ID) and ETA, ATA, ETD, ATD for each voyage, among other info.

If I want to know the duration of the voyage 75,  I have to do (ATA from voy 76) - (ATD from voy 75)

Can you help with na DAX expression to calculate that?

Thanks in advance.

[Anonymous]

@SofiaVaz 
Create a calculated column as above. 
You can also convert it into a measure.

Hope this helps.

Thanks

[amitchandak]

@SofiaVaz ,Can you share sample data and sample output in table format?

In this blog check -How can we get last non continuous day in a new calculated column?

a very similar approach would be needed

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[SofiaVaz]

Hi amitchandak 

I'm sorry I wasn't clear. Here's a sample. I'm trying to find and expression that will give the navigation time of a certain trip (knowing that each trip is time in port + time sailing)

[richbenmintz]

Hi @SofiaVaz 

Below please find a calculated column that i think gets you what you are looking for.

It will get the duration for the vessel in the current row context based on the prior voyage number. you would have to update the formula to get the correct voyage number if they are not consecutive as your data suggests.

@SofiaVaz, found a bug and updated, sorry, got the fields and order backwards

Navigation Time = 
var vessel = 'Table'[Vessel]
var voyageNum = 'Table'[Voy #]
var startDate = CALCULATE(max('Table'[ATA]), filter(ALL('Table'), [Voy #] = voyageNum +1 && 'Table'[Vessel] = vessel))
return
DATEDIFF('Table'[ATD],startDate , MINUTE)

Hope this helps,

Richard

[Fowmy]

@SofiaVaz 

Can you share some sample data and the expected result as data to have a clear understanding of your question?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[SofiaVaz]

Hi Fowmy, 

I'm sorry I wasn't clear. Here's a sample. I'm trying to find and expression that will give the navigation time of a certain trip (knowing that each trip is time in port + time sailing)

[Fowmy · Accepted solution]

@SofiaVaz 

Based on how I understood, I created the following sample data set to match your data and added a column to get the time
difference.

NAV Time = 
VAR NEXT_ATA = 
CALCULATE(
    MIN(VOYAGE[ATA]),
    ALLEXCEPT(VOYAGE,VOYAGE[VESSEL]),
    VOYAGE[ATA]>EARLIER(VOYAGE[ATA])
)

VAR CURRENT_ATD = 

[ATD]

RETURN
IF( 
    ISBLANK(NEXT_ATA), 
    BLANK(), 
    FORMAT(CURRENT_ATD - NEXT_ATA,"hh:mm:ss")
)