Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DATEDIFF between DateTime Values by ID

I have a Table_A, which tracks the DateTime of status changes. DateTime ID Entry Exit 29/10/2020 14:25 1001 1 0 01/12/2020 15:30 1002 1 0 05/02/2021 12:00 1001 0 1 15/...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try a measure like

     

    averageX(values(Table[ID]), calculate(datediff(minx(filter(Table, Table[Entry] =1), [DateTime]),maxx(filter(Table, Table[exit] =1), [DateTime]),minute)))

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

Anonymous , Try a measure like

 

averageX(values(Table[ID]), calculate(datediff(minx(filter(Table, Table[Entry] =1), [DateTime]),maxx(filter(Table, Table[exit] =1), [DateTime]),minute)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amitchandak,
    Is it possible to filter this measure [TimeSpent] by a Date table 'Date'?
    You would need to specify in the Measure that the filter applies to the Exit DateTime. Or possibly ignore the lesser date with something like ...

    FILTER(ALL('Date'[DateTime]),'Date'[DateTime]<=MAX('Date'[DateTime]))