Calculated Column and refering to row values.

Twi78

New Member
Joined
Mar 27, 2012
Messages
18
I have the following table:


Event Part_Number Month Year Date
10598 ADF345 1 2012 15/01/2012
10599 ADF345 1 2012 29/01/2012
10600 ADF345 2 2012 15/02/2012
10601 ADF345 2 2012 29/02/2012
10602 EFT689 1 2012 15/01/2012
10603 EFT689 2 2012 29/02/2012
10604 DBM165 1 2012 15/01/2012
10605 DBM165 1 2012 29/01/2012
10606 WTP726 1 2012 15/01/2012
10607 WTP726 1 2012 29/01/2012
10608 WTP726 2 2012 29/02/2012

What I am trying to achieve:

The 'event' is in numerical order and unique and signifies the process flow of a part. What I want a calculated field to do to is to flag which is the maximium 'event' number for each YEAR/MONTH/Part_Number by returning the event number on each row, using the fields in each row to provide the filter data. For example row 1 & 2 would return '10599', and where the event and the calculated field equal, this will tell me which is the closing position for each month.

I can only get this to work by hard coding the year/month/PN into the filter elements of the CALCULATE function, but refering to the field name provides incorrect results.

If this were a tbale in excel you would use something like [THIS ROW] to pass the field values of the current row through the formula.

Any ideas - really scratching my head on this one!:confused:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Assuming a table called Table1, I think this would do it:

=maxx(filter(Table1,[Part_Number] = earlier([Part_Number]) && [Month] = EARLIER([Month]) && [Year] = EARLIER([Year])),[Event])
 
Upvote 0
I think this approach is simpler.
Code:
=CALCULATE(MAX([Event]),ALLEXCEPT(Table1,Table1[Year],Table1[Month],Table1[Part_Number]))
It works in the latest release (SQL 2012 RTM) but I think it may not work on SQL 2008 R2 because CALCULATE worked somewhat differently when used in a calculated column
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top