Forum Discussion
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.
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
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])Best Regards,
Jay
9 Replies
- amitchandak
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.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?
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, were you able to open the PBIX file I shared with the table? I'm still looking for a solution.
Thank you
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
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/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
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])Best Regards,
Jay
Awesome! Thanks for your help. It works great.
