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.
Hello All,
I have been stuck with this issue for a while and i would appreciate some help. I have got the following table.
ID | Date |
1 | 16/07/2020 14:11:12 |
1 | 17/07/2020 15:12:11 |
1 | 16/07/2020 15:32:44 |
1 | 16/07/2020 17:11:24 |
2 | 19/07/2020 06:11:12 |
2 | 19/07/2020 12:12:11 |
2 | 20/07/2020 14:32:44 |
2 | 20/07/2020 02:11:24 |
I would like to calculate the time by using the minimum value for each ID and subtracting it for rows that hold the same ID. However, i want the format to be in hh:mm:ss where if the difference is more than 24 hours it would be 37:44:22 for example.
Here is how i would like the results should look like.
ID | Date | time |
1 | 16/07/2020 14:11:12 | 00:00:00 |
1 | 17/07/2020 18:32:23 | 28:21:11 |
1 | 16/07/2020 18:32:44 | 04:21:32 |
1 | 16/07/2020 17:11:24 | 00:00:12 |
2 | 19/07/2020 06:11:12 | 00:00:00 |
2 | 19/07/2020 12:12:12 | 06:01:00 |
2 | 20/07/2020 14:32:44 | 26:20:32 |
I would prefer to do in the power query, as i tried doing something similiar in DAX, and after uploading it to power bi service the time axis isnt as expected as it assumes 00:00:00 is 12:00 AM. If done in power query the column type could be changed however.
Hi @Adham ,
Create a measure as below:
Time =
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])))
var _daydiff=DATEDIFF(_mindate,MAX('Table'[Date]),HOUR)
Return
_daydiff&":"&FORMAT(MAX('Table'[Date])-_mindate,"nn:ss")
And you will see:
For details,pls see attachment.
Hello @v-kelly-msft,
Thank you for your answer. I need to plot the time as the x-axis on a line graph, and it doesnt seem possible with a measure. Is there something else i could do? Maybe alter this measure to be a column?
Hi @Adham ,
Yes,change the measure into calculated column :
_Time =
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))
var _daydiff=DATEDIFF(_mindate,'Table'[Date],HOUR)
Return
_daydiff&":"&FORMAT('Table'[Date]-_mindate,"nn:ss")
For modified .pbix file,pls see attached.
Hello @v-kelly-msft,
I actually just realised something. If you like at the table below you will see something odd when i apply the formula to have a column with the times.
Date | Calculated Time | Should be |
21/07/2020 14:44:19 | 0:00:00 | |
21/07/2020 15:00:00 | 1:15:41 | 0:15:41 |
21/07/2020 15:44:19 | 1:00:00 | |
21/07/2020 15:55:19 | 1:11:11 |
When the hour in the datetime stamp hits 15 it assumes an hour has passed which is incorrect. It will assume an hour has passed all the way till 15:44:18 where the correct time is calculated and adjusted at 15:44:19. However, when the time hits 16:00:00 the calculated time difference will be 2:15:41 and so on. This trend is observed throughout the entire column. How can i fix this issue?
I have posted a question here https://community.powerbi.com/t5/Desktop/Plotting-Duration-as-continuous-line-graph/m-p/1243805#M549...
I would really appreciate if you an give me a hand.
Thank you,
Adham
Would it be possible to also show me the power query version of this? I want to change the type to duration, then hopefully be able to plot it on a line graph as continuous. Currently i can only plot it as categorical as this is done in dax.
@Adham , Try like
time = Table[Date] -maxx(filter(Table,Table[Date] <earlier(Table[Date]) && Table[ID] =earlier(Table[ID])),Table[Date])
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |