I have been stuck with this issue for a while and i would appreciate some help. I have got the following table.
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.
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.
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.
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|
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.
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.