cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
unc49us
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
v-jayw-msft
Community Support
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.

View solution in original post

v-jayw-msft
Community Support
Community Support

Hi @unc49us ,

 

Please refer the formula.

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])

1.PNG

 

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.

View solution in original post

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

Hi @unc49us ,

 

Please refer the formula.

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])

1.PNG

 

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.

Awesome! Thanks for your help. It works great. 

v-jayw-msft
Community Support
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.

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. 

Comments         Date        Time      Set                            Diff

N/A2/2/20225:13:04 PM18110531200:35
N/A2/2/20225:14:23 PM18109531201:19
N/A2/2/20225:14:58 PM24832521600:35
N/A2/2/20225:15:34 PM18108531200:36
N/A2/2/20225:16:38 PM24831521601:04
N/A2/2/20225:17:13 PM18107531200:35
End of day2/2/20225:17:47 PM24830521600:34
Start of day2/3/20227:46:19 AM18105531200:00
N/A2/3/20227:46:54 AM24828521600:35
N/A2/3/20227:48:06 AM18104531201:12
N/A2/3/20227:48:40 AM24829521600:34
N/A2/3/20227:49:21 AM18103531200:41
N/A2/3/20227:50:13 AM25116522400:52
N/A2/3/20227:50:48 AM18102531200:35
N/A2/3/20227:51:56 AM18101531201:08
N/A2/3/20227:52:54 AM25117522400:58
N/A2/3/20227:53:29 AM18100531200:35
unc49us
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

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

 

amitchandak
Super User
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 !!

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? 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors