Calculate Difference in Time within Column with change in dates

[unc49us]

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.

[v-jayw-msft · Accepted solution]

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

[v-jayw-msft · Accepted solution]

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

[unc49us]

Awesome! Thanks for your help. It works great. 

[v-jayw-msft · Accepted solution]

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

[unc49us]

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

[unc49us]

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

Thank you

[unc49us]

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]

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

[unc49us]

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? 

[unc49us]

Sample Data Production Report