Please Help a newbie with this following simple calculation problem

[Vrykolakas]

I'm experimenting around, and I'm a little bit struggling with this one. Would really appreciate if anyone could enlighten me on how to solve the following :

My table is as shown :

For each rented car, there's a Car-ID, reservation number ( that goes ASC ), and of course a start and end time.

What I'm trying to find here, is how long ( mins ) did the car stay still between each reservation.. 

My logic is as following, but I'm having difficulties writting it correctly. (so Please feel free to correct my mistakes, or open my mind to other ways) :
For each CarID, and for each Reservation, I need to calculate the periode between  End-Time of Resa1 and Start-Time of Resa2.
Now the only way I have to get the Resa2 for each Resa1, is maybe via Ranking ?

I'm stuck with this since two days, and it's frustrating.
Thanks in advance for any help 🙂

[mahoneypat · Accepted solution]

Please try this measure expression. It calculates the overall time, the used time, and then subtracts them to get the idle time.

Idle Time =
VAR totaltime =
    MAX ( Trip[End_time] ) - MIN ( Trip[Start_time] )
VAR usedtime =
    SUMX ( Trip, Trip[End_time] - Trip[Start_time] )
VAR result =
    FIXED ( usedtime, 3 )
RETURN
    result

Also, please check out this article. 

Calculate and Format Durations in DAX – Hoosier BI

Pat

[v-stephen-msft · Accepted solution]

Hi @Vrykolakas ,

You want to calculate the difference between the end time of the previous row and the start time of the current row, right?

So using a rank column is a good idea! 

After rank column is created, create another column.

Period = var _end=CALCULATE(MAX('Trip'[End_time]),FILTER('Trip',[Rank]<EARLIER(Trip[Rank])))
return IF(ISBLANK(_end),BLANK(),FORMAT([Start_time]- _end,"HH:MM:SS") )

Best Regards,

Stephen Tao

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

[smpa01]

@Vrykolakas  can you please provide the sample data in a table format please? or upload the sample pbix in 1/g drive and share the link here?

[Vrykolakas]

Hey @smpa01 
Thank you for getting back 🙂
I'm still stuck trying to solve this..

PBI file can be found HERE

I did create this report only for the question I asked here. The original pbix includes way more data... 

[mahoneypat · Accepted solution]

Please try this measure expression. It calculates the overall time, the used time, and then subtracts them to get the idle time.

Idle Time =
VAR totaltime =
    MAX ( Trip[End_time] ) - MIN ( Trip[Start_time] )
VAR usedtime =
    SUMX ( Trip, Trip[End_time] - Trip[Start_time] )
VAR result =
    FIXED ( usedtime, 3 )
RETURN
    result

Also, please check out this article. 

Calculate and Format Durations in DAX – Hoosier BI

Pat