Forum Discussion

JCKong's avatar
JCKong
Frequent Visitor
3 years ago
Solved

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 o...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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

JCKong's avatar
JCKong
Frequent Visitor
3 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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

    • JCKong's avatar
      JCKong
      Frequent Visitor
      3 years ago

      Thank you tamerj1 , this did work without the index column which increases the file size.

  • JCKong's avatar
    JCKong
    Frequent Visitor
    3 years ago

    Used a measurement to Sum(Table[Duration]) as the Duration column above only shows earliest and there's no selection for SUM.