Forum Discussion

PaulGiscard's avatar
PaulGiscard
New Member
3 years ago

Operation using the previous row data

Hi all,   Appologies if this has been answered elsewhere but I can't find a solution that works for me in the other posts...    My problem is I need to add data and use result in the next row.  ...
Need Help
Tips and Tricks
artemus's avatar
artemus
Microsoft Employee
3 years ago

I'm not sure why you think it would take 4 times... but I don't see your dataset.

 

Another option is to adjust the week no for closing balance rows to match the other rows, and then do a pivot to get a column for each event type in a week.

  • PaulGiscard's avatar
    PaulGiscard
    New Member
    3 years ago

    Hi Artemus, I must not understand properly your steps then...

     

    Here is the data I'm working with:

    A query with my week numbers 

    A query which has my total pallets in and total pallets out for the different weeks of my data set (some weeks might be 0 and therefore don't show up in that table)

    A query which is drilled down to return the opening balance for the first week.

     

    If Week number is n, n+1, etc... I need to create the following table:

    Where Opening balance week n is OPWn, Storage is SWn, Closing balance is CBWn

    Pallets in week n is PIWn, Pallets out Week n is POWn

    Opening balance for the first week is x (manual input)

     

    Week numberOpening balanceStorageClosing balance
    nxSWn= x + PIWnCBWn = SWn - POWn
    n+1OPWn+1 = CBWnSWn+1 = OPWn+1 +PIWn+1CBWn+1 = SWn+1 - POWn+1
    n+2OPWn+2 = CBWn+1SWn+2 = OPWn+2 +PIWn+2CBWn+2 = SWn+2 - POWn+2

     

    I hope that makes more sense and sorry if the solution is the same, I just can't work out what your instructions mean for my data...