Forum Discussion
Difference between rows by Name by date
I can't figure out how to do this in dax column, getting the duration difference from the next row per name.
The table is already sorted by Name then by shift date then by punch time in ascending order.
Hi JCKong
please try
Duration =
VAR CurrentTime = 'Table'[Punch Time]
VAR CurrentNameDateTable =
CALCULATETABLE (
VALUES ( 'Table'[Punch Time] ),
ALLEXCEPT ( 'Table', 'Table'[Name], 'Table'[Short Date] )
)
VAR TableAfter =
FILTER ( CurrentNameDateTable, 'Table'[Punch Time] > CurrentTime )
VAR NextTime =
MINX ( TableAfter, 'Table'[Punch Time] )
RETURN
COALESCE ( NextTime, CurrentTime ) - CurrentTime
6 Replies
Currently I use this but some of the Names doesn't show their duration:
Duration = VAR NextRow = CALCULATE ( SUM (Table[PunchTime]), FILTER (Table, Table[Index] = EARLIER (Table[Index]) + 1))) RETURN IF(Table[Activity] = "Logout", 0 , NextRow - Table[PunchTime])
- tamerj1
Community Champion
Hi JCKong
please try
Duration =
VAR CurrentTime = 'Table'[Punch Time]
VAR CurrentNameDateTable =
CALCULATETABLE (
VALUES ( 'Table'[Punch Time] ),
ALLEXCEPT ( 'Table', 'Table'[Name], 'Table'[Short Date] )
)
VAR TableAfter =
FILTER ( CurrentNameDateTable, 'Table'[Punch Time] > CurrentTime )
VAR NextTime =
MINX ( TableAfter, 'Table'[Punch Time] )
RETURN
COALESCE ( NextTime, CurrentTime ) - CurrentTime Used a measurement to Sum(Table[Duration]) as the Duration column above only shows earliest and there's no selection for SUM.
- tamerj1
Ctreate a Punch DateTime column by adding Date and Time then use the following
Duration =
VAR CurrentTime = 'Table'[Punch Time]
VAR CurrentNameTable =
CALCULATETABLE (
VALUES ( 'Table'[Punch Time] ),
ALLEXCEPT ( 'Table', 'Table'[Name] )
)
VAR TableAfter =
FILTER ( CurrentNameTable, 'Table'[Punch DateTime] > CurrentTime )
VAR NextTime =
MINX ( TableAfter, 'Table'[Punch DateTime] )
RETURN
IF (
'Table'[Activity] = "Logout",
0,
COALESCE ( NextTime, CurrentTime ) - CurrentTime
)
