cancel
Showing results for
Did you mean:
Frequent Visitor

Calculate Difference in Time within Column with change in dates

I have a table where i have multiple times throughout a day. I'm trying to calculate the difference between each time change in that day. I also need to start from 0:00 time at each change of date. Is there a formula through dax that would work? I had a formula that worked in excel but will not work within BI.  Here is the excel formula: =IF(ISNUMBER(SEARCH("Start",[@Comment])),0,E5-E6).

Any help would be appreciated.

2 ACCEPTED SOLUTIONS
Community Support

Hi @unc49us ,

Can't reach the sample data you shared above.

Are you want to calculate the time difference between two rows in a single day?

For exapmle:

2022/2/20 8:00:00

2022/2/20 10:00:00 2 hours

2022/2/20 14:00:00 4 hours

2022/2/21 9:00:00

2022/2/21 12:00:00 3 hours

It is better to share some sample and expected result.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Community Support

Hi @unc49us ,

``````Column 2 =
var _previous = CALCULATE(MAX('Table'[time]),FILTER('Table','Table'[date]=EARLIER('Table'[date])&&'Table'[time]<EARLIER('Table'[time])))
return
IF(ISBLANK(_previous),TIME(0,0,0),_previous-'Table'[time])``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
9 REPLIES 9
Community Support

Hi @unc49us ,

``````Column 2 =
var _previous = CALCULATE(MAX('Table'[time]),FILTER('Table','Table'[date]=EARLIER('Table'[date])&&'Table'[time]<EARLIER('Table'[time])))
return
IF(ISBLANK(_previous),TIME(0,0,0),_previous-'Table'[time])``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Frequent Visitor

Awesome! Thanks for your help. It works great.

Community Support

Hi @unc49us ,

Can't reach the sample data you shared above.

Are you want to calculate the time difference between two rows in a single day?

For exapmle:

2022/2/20 8:00:00

2022/2/20 10:00:00 2 hours

2022/2/20 14:00:00 4 hours

2022/2/21 9:00:00

2022/2/21 12:00:00 3 hours

It is better to share some sample and expected result.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Frequent Visitor

Below is an example of the table contents and what I'm looking for. I am wanting to calculate the difference in time from one row to the next and whenever there is a date change it inputs zero for the first record.

 N/A 2/2/2022 5:13:04 PM 1 81105312 00:35 N/A 2/2/2022 5:14:23 PM 1 81095312 01:19 N/A 2/2/2022 5:14:58 PM 2 48325216 00:35 N/A 2/2/2022 5:15:34 PM 1 81085312 00:36 N/A 2/2/2022 5:16:38 PM 2 48315216 01:04 N/A 2/2/2022 5:17:13 PM 1 81075312 00:35 End of day 2/2/2022 5:17:47 PM 2 48305216 00:34 Start of day 2/3/2022 7:46:19 AM 1 81055312 00:00 N/A 2/3/2022 7:46:54 AM 2 48285216 00:35 N/A 2/3/2022 7:48:06 AM 1 81045312 01:12 N/A 2/3/2022 7:48:40 AM 2 48295216 00:34 N/A 2/3/2022 7:49:21 AM 1 81035312 00:41 N/A 2/3/2022 7:50:13 AM 2 51165224 00:52 N/A 2/3/2022 7:50:48 AM 1 81025312 00:35 N/A 2/3/2022 7:51:56 AM 1 81015312 01:08 N/A 2/3/2022 7:52:54 AM 2 51175224 00:58 N/A 2/3/2022 7:53:29 AM 1 81005312 00:35
Frequent Visitor

Amitchandak, were you able to open the PBIX file I shared with the table? I'm still looking for a solution.

Thank you

Frequent Visitor

What is the best way for me to share the pbix file with you? I've removed the sensative data, but am having trouble uploading it to the forum.

Super User

@unc49us , Try a new column like

datediff(maxx(filter(Table, [datetime] < earlier([datetime])),[datetime]),[datetime], second)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!
Frequent Visitor

You can see I tried the formula and it worked to get the seconds, but when the date changed it was still trying to calculate the difference from the previous row. I'm looking to start each day at zero.

Also I tried changing the format to h:mm:ss. When I made this adjustment it showed 0:00:00. Is there a way to make the change to format it?

Frequent Visitor