Forum Discussion

Adham's avatar
Adham
Helper III
6 years ago

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...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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,
Kelly
Did I answer your question? Mark my post as a solution!
  • Adham's avatar
    Adham
    Helper III
    6 years ago

    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?

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      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,
      Kelly
      Did I answer your question? Mark my post as a solution!

       

      • Adham's avatar
        Adham
        Helper III
        6 years ago

        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.