Forum Discussion

Matt_P's avatar
Matt_P
Helper I
6 years ago

Grouping / Aggregation

I have the data below and was wondering how to calculate the diff column which is previous qty minus current row quantitiy while ensuring ID is the same.  Many thanks!   ID RecordDate Accumila...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Matt_P 

Hi,

I suggest you to create an Index column, this can make it very simple with the following formula:

 

Result =
VAR current_ = [Index]
RETURN
    IF (
        Sheet1[Index] <> 1,
        [Accumilated Qty]
            - CALCULATE (
                MAX ( [Accumilated Qty] ),
                FILTER ( Sheet1, Sheet1[Index] = current_ - 1 ),
                FILTER ( Sheet1, Sheet1[Index] <> 0 )
            ),
        BLANK ()
    )

 

Best,
Paul

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Matt_P's avatar
    Matt_P
    Helper I
    6 years ago

    Anonymous this looks like exactly what I'm looking for.  Ill try it out when i get the chance and report back/accept as soution.  Many Thanks!

     

    edit.  Actually, this wouldnt work if I had an id of 4 in the same dataset as the id of 5 would it?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Matt_P 

      This formula is created by looking at the the index column, so it does not matter if your ID is 4 or 5. 
      Just leave a message for any updates if needed.

       

      Best,