cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
omarevp Regular Visitor
Regular Visitor

Calculate time difference from two dates that come from the same column

Greetings, as common, I really need your help. I need to compare a column with itself, in order to calculate duration in hh:mm:ss.

 

Here’s what I want: I have a db with these columns: driving hours, working hours and resting hours for each driver; this db registers each activity by separate as follow, with a timestamp called FechaHora:

Captura.PNG

 

If I create in report view the following, with a table visual, I can show it this way, more or less what I need:

Captura1.PNG

 

What i did in this table was to take the same field value, twice in the same table, the first one shows the First FechaHora (*) and the second one shows the Last FechaHora (**) of the same day, with an additional Date field that shows only date in order to separate each one. Now, I wish to calculate the hours difference (Horas Diff) between the Last FechaHora from the previous date and the First FechaHora from the next day. Knowing that both dates are from the same column. Here´s what I need to do, and being able to filter it by Driver (Conductor): 

Captura2.PNG

 

I hope I explained myself, because I know it’s a little confusing. I need the difference (in hours) between the Last FechaHora from the previous date and the First FechaHora from the next day. I repeat, both dates are from the same column, called FechaHora. THANKS!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate time difference from two dates that come from the same column

Hi @omarevp

Create measures

earliest = CALCULATE(MIN([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])))

last = CALCULATE(MAX([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])))

Measure = CALCULATE(MAX([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])-1)) duration = var last2=CALCULATE(MAX([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])-1))
return IF(ISBLANK(last2),BLANK(),[earliest]-last2)

[Measure] is equal to "last2" in the "duration" formula, it just let you know how it works, it is no need to create this measure in your table.

 

6.png

 

Best Regards

maggie

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Calculate time difference from two dates that come from the same column

Hi @omarevp

Create measures

earliest = CALCULATE(MIN([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])))

last = CALCULATE(MAX([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])))

Measure = CALCULATE(MAX([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])-1)) duration = var last2=CALCULATE(MAX([datetime]),FILTER(ALLEXCEPT(Sheet4,Sheet4[category]),[date]=MAX([date])-1))
return IF(ISBLANK(last2),BLANK(),[earliest]-last2)

[Measure] is equal to "last2" in the "duration" formula, it just let you know how it works, it is no need to create this measure in your table.

 

6.png

 

Best Regards

maggie

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 119 members 1,555 guests
Please welcome our newest community members: