How to take last available data in group in calculated field?

[kaplak]

Dear Guys,

I have a problem in here,

I want to have some calculated field that will check the waste level of material.

the calculation of waste is :
WASTE = ( Sum (ACTUAL_MATERIAL_USAGE) - Sum (THEORETICAL_MATERIAL_USAGE) - STOCK_IN_PROCESS )/
Sum (THEORETICAL_MATERIAL_USAGE)

The Column that I have are :
MONTH | WEEK | DATE | THEORETICAL_MATERIAL_USAGE | ACTUAL_MATERIAL_USAGE | STOCK_IN_PROCESS

all the data will be generated by day except for the STOCK_IN_PROCESS.
STOCK_IN_PROCESS is only available for the closing of accounting (which is weekly and monthly)

thats why the stock in process that I want is actually the last data available within group (sorted by date) for the pivoting view.
(i mean when i see pivoting as weekly STOCK_IN_PROCESS for the calculation is only for the last day of that week, not the sum within that week. So do when i see pivoting as a monthly view)

Is there any way to do this.
As far as i know that the 'fieldname' that is used in calculated field is only showing the sum of that field by grouping (week / month).

Your help will mean a lot to me.

Thx.