Calculate difference between latest shift date/time and previous shift date/time - direct query data

[Anonymous]

I am using direct query to bring in time and attendance data into my data model.  Import would be impractical due to the size of the dataset. 

I need to be able to compare shifts worked for each employee with previous shift worked, and calculate the time difference between the starting time of the 2 shifts. 

Is this even possible using direct query, with the limitations on using calculated columns etc?

[Anonymous]

Thank you so much this is perfect

[amitchandak · Accepted solution]

@Anonymous ,

No you can not have a calculated column for that, You have to create a measure

Measure =

var _max = maxx(filter(allselected(Table), Table[Employee] = max(Table[Employee]) && Table[Pay Start] < max(Table[Pay Start])) , Table[Pay Start])
return
datediff(_max, max(Table[Pay Start]), day)

If needed use one more measure

sumx(Summarize( Table, Table[Employee],Table[Pay Start]), [Measure])

use averageX if needed