Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 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       Exampl...
Need Help
  • Anonymous's avatar
    Anonymous
    8 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

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 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
    8 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
      8 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you so much for help