Calculated Column and refering to row values.

[Twi78]

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!

 

[David Churchward]

Hi

Looks to me that you need to use EARLIER().

The post below should give you the right direction:

http://www.powerpivotpro.com/2012/02/peak-detection-a-surprising-usage-of-powerpivot/

Hope this helps

 

[RoryA]

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])

 

[ruve1k]

I think this approach is simpler.

=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