Datediff between columns, grouped by users

[mtrevisiol]

Hi everyone. I've got this table:

which contains only columns from my dataset.

I've grouped it by OpID by summarizing the table selecting the oldest TimeStart and the most recent TimeStop:

Now my purpose is to calculate the difference between "Prime TimeStart" (the oldest) and "Ultime TimeStop" (the most recent), by creating a measure or a calculated column. How can I do it? Here is the file: https://www.dropbox.com/s/5yurqav2acq9vlw/datediff.pbix?dl=0

Thanks!

[Anonymous]

if you want it per OpID

i will try calculated columns : (if you want it in minutes)

diff =
DATEDIFF([Ultime TimeStop] ), [Prime TimeStart]), MINUTE )

https://docs.microsoft.com/en-us/dax/datediff-function-dax 

[mtrevisiol]

@Anonymous 

thanks but the problem is that "Prime Time Start" and "Ultime Time Stop" are not columns. They represent the way the rows are grouped: for the oldest starttime and the most recent stoptime:

[Anonymous]

Can you group them in PowerQuery ? (instead of in the visual)

[mtrevisiol]

@Anonymous maybe I can duplicate the table and then modify it in PowerQuery. How do tou suggest to do it?

[v-easonf-msft · Accepted solution]

Hi, @mtrevisiol 

Try formula as below:

Diff = CALCULATE(MAX(data[TimeStop]))-CALCULATE(MIN(data[TimeStart]))

Best Regards,
Community Support Team _ Eason

[amitchandak]

@mtrevisiol , Try measure like

sumx(values(Table[OpId]), datediff(min(Table[prime start time]), max(Table[Ultime TimeStop])))

or

calculate(sumx(values(Table[OpId]), datediff(min(Table[prime start time]), max(Table[Ultime TimeStop]))), allexcept(Table, Table[OpId]))

[mtrevisiol]

@amitchandak thanks but the problem is that "Prime Time Start" and "Ultime Time Stop" are not columns. They represent the way the rows are grouped: for the oldest starttime and the most recent stoptime: