cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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 🙂

2 ACCEPTED SOLUTIONS
Microsoft

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 ( usedtime3 )
RETURN
result

Calculate and Format Durations in DAX – Hoosier BI

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Community Support

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.

4 REPLIES 4
Community Support

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.

Super User

@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?

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Frequent Visitor

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...

Microsoft

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 ( usedtime3 )
RETURN
result

Calculate and Format Durations in DAX – Hoosier BI

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors