Forum Discussion
Calculate difference between latest shift date/time and previous shift date/time - direct query data
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 ,
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 measuresumx(Summarize( Table, Table[Employee],Table[Pay Start]), [Measure])
use averageX if needed
2 Replies
- amitchandak
Super User
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 measuresumx(Summarize( Table, Table[Employee],Table[Pay Start]), [Measure])
use averageX if needed