Forum Discussion
Calculate time between dates for each unique identifier
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.
8 Replies
Adham , Try like
time = Table[Date] -maxx(filter(Table,Table[Date] <earlier(Table[Date]) && Table[ID] =earlier(Table[ID])),Table[Date])
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.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!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.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- Power Query has a duration data type, but this is converted to decimal number when loaded into the report: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types
See if this link helps you calculate the difference between two rows using Power Query: https://www.myonlinetraininghub.com/referencing-next-row-power-query
