- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Calculate time difference from two dates that come from the same column
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
10-15-2018 01:59 PM
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:
If I create in report view the following, with a table visual, I can show it this way, more or less what I need:
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):
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!
Solved! Go to Solution.
Accepted Solutions
Re: Calculate time difference from two dates that come from the same column
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
10-16-2018 12:27 AM
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.
Best Regards
maggie
All Replies
Re: Calculate time difference from two dates that come from the same column
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
10-16-2018 12:27 AM
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.
Best Regards
maggie