Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
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 |
Amitchandak, were you able to open the PBIX file I shared with the table? I'm still looking for a solution.
Thank you
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.
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |