Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Difference between two rows with previous day, same Work Item Id

Completed Daily = for a given row, it is the difference between the Completed Work column and the previous value (previous day, same Work Item Id)

 

http://prntscr.com/ktxpn4

 

 

 

Example: 

 

For Work ID: 85643

 

I need result:

First Row: 0

Scond Row: 2.5

Third Row: 4.5-2.5 = 2

Forth Row 9.00-4.5= 4.50

 

Need to subtracted above row value with group by of column (Work ID and Date) 

 

Reult look like this:

 

 

 

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    You can try to use below calculate column formula to achieve your requirement:

    Completed Daily =
VAR previous =
    CALCULATE (
        MAX ( Test[Changed Date] ),
        FILTER (
            ALL ( Test ),
            [Work Item Id] = EARLIER ( [Work Item Id] )
                && [Changed Date] < EARLIER ( Test[Changed Date] )
        )
    )
RETURN
    [Work]
        - LOOKUPVALUE (
            Test[Work],
            Test[Work Item Id], [Work Item Id],
            Test[Changed Date], previous
        )

     

    Regards.

    Xiaoxin Sheng

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous

     

    Try this calculated column

     

    Completed Daily =
[work]
    - MINX (
        TOPN (
            1,
            FILTER (
                table1,
                [Work Item Id] = EARLIER ( [Work item Id] )
                    && [Changed date] > EARLIER ( [Changed date] )
            ),
            [Changed Date], ASC
        ),
        [Work]
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Zubair_Muhammad

       

      Thanks This logic helpful. 

       

      But Stil have one problem. It show result in Wrong column. 

      it show result in wrog rows.

       

      First Row show have result 0 and second row result is -2.5 like this 
      0

      2

      2.5

      4.5

      0

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous,

         

        You can try to use below calculate column formula to achieve your requirement:

        Completed Daily =
VAR previous =
    CALCULATE (
        MAX ( Test[Changed Date] ),
        FILTER (
            ALL ( Test ),
            [Work Item Id] = EARLIER ( [Work Item Id] )
                && [Changed Date] < EARLIER ( Test[Changed Date] )
        )
    )
RETURN
    [Work]
        - LOOKUPVALUE (
            Test[Work],
            Test[Work Item Id], [Work Item Id],
            Test[Changed Date], previous
        )

         

        Regards.

        Xiaoxin Sheng