Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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