Forum Discussion

backflash's avatar
backflash
Icon for Helper II rankHelper II
4 years ago
Solved

Calculate difference between two rows with dependencies

Hey All,   hope someone can help me with the following: I want to calculate the difference between two rows - but I want to do this only for rows where some Column-values are the same. (btw: I al...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi backflash 

    Break Time =
VAR CurrentStart = Table[Start]
VAR CurrentEnd = Table[End]
VAR PreviousEnd =
    MAXX (
        FILTER (
            CALCULATETABLE (
                Table,
                ALLEXCEPT ( Table, Table[Emplyee Number], Table[Date] )
            ),
            Table[End] < CurrentEnd
        ),
        Table[End]
    )
RETURN
    IF ( 
        NOT ISBLANK ( PreviousEnd ),
        CurrentStart - PreviousEnd
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi backflash 

you create a calculated column

Break Time =
VAR CurrentStart = Table[Start]
VAR CurrentEnd = Table[End]
VAR PreviousEnd =
    MAXX (
        FILTER (
            CALCULATETABLE (
                Table,
                ALLEXCEPT ( Table, Table[Emplyee Number], Table[Date] )
            ),
            Table[End] < CurrentEnd
        ),
        Table[End]
    )
RETURN
    CurrentStart - PreviousEnd
  • backflash's avatar
    backflash
    Icon for Helper II rankHelper II
    4 years ago

    Hey tamerj1 ,

     

    thanks a lot - your code for the calculated column is working fine.

     

    But I now have a break time on each new day (from 0:00 to start time)  - do you have an idea how I could have the cells of the break time empty, when there is a new day?

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi backflash 

      Break Time =
VAR CurrentStart = Table[Start]
VAR CurrentEnd = Table[End]
VAR PreviousEnd =
    MAXX (
        FILTER (
            CALCULATETABLE (
                Table,
                ALLEXCEPT ( Table, Table[Emplyee Number], Table[Date] )
            ),
            Table[End] < CurrentEnd
        ),
        Table[End]
    )
RETURN
    IF ( 
        NOT ISBLANK ( PreviousEnd ),
        CurrentStart - PreviousEnd
    )