Earliest Date by Product Problems

[robtop]

Morning,

I have a table with a range of transactions for different products.

One of the columns is DateOfTransaction.

I'm trying to get the earliest transaction date for each product but what I'm actually getting is just the earliest transacton date overall.

I am expecting results to look like this:

Product Earliest Transaction Date

Shop Insurance 12/01/2012
Office Insurance 01/01/2012
Business Insurance 03/03/2012


However what i'm getting is:

Product Earliest Transaction Date

Shop Insurance 01/01/2012
Office Insurance 01/01/2012
Business Insurance 01/01/2012

The measure I'm using is as follows:

Earliest Product Trans Date:=calculate(MIN([DateOfTransaction]),[Product])

Any idea where I'm going wrong or what i need to do to fix it?

Thanks very much

Rob

 

[Marcol]

Is this what you need?

Drag E2 Down

Excel Workbook
	A	B	C	D	E
1	Product	Transaction Date		Product	Earliest Transaction Date
2	Shop Insurance	12/01/2012		Shop Insurance	11/01/2011
3	Office Insurance	01/01/2012		Office Insurance	01/01/2012
4	Business Insurance	03/03/2012		Business Insurance	03/01/2012
5	Shop Insurance	11/01/2011
6	Office Insurance	01/05/2012
7	Business Insurance	03/01/2012
8
9
10
Sheet1

 

[Zakkaroo]

Could you just use Min - i.e.

Assuming:
Column A = Product Description
Column B = Date of transaction

Column F = The product name that you are looking up


The following formula (just an example with a small set of data, can change the numbers as needed) would go in the result cell, but after typing, you must enter the formula with Ctrl + Shift + Enter instead of just pressing Enter

=MIN(IF(A2:A6=F2,B2:B6))

 

[Marcol]

@ zakkarro

Your solution is fine, but for larger sheets array entered formulae can prove very expensive.

Here's the opinion of one MS MVP, on another forum that shall remain nameless ...

Perhaps the biggest problem with array formulae is that they look efficient, but when compared to an alternative, nothing could be further from the truth! An array formulae must follow rules that Excels built in Functions do not have to, that is they must loop through each and every cell they reference (one at a time) and check them off against a criteria. For this reason arrays are best suited to being used on single cells or referencing only small ranges.

 

[Zakkaroo]

@ zakkarro

Your solution is fine, but for larger sheets array entered formulae can prove very expensive.

Here's the opinion of one MS MVP, on another forum that shall remain nameless ...

Thanks for the info Marcol ! I learn something every day

 

[robtop]

Thanks, but I think they are solutions for an excel table whereas I'm trying to do this in a Powerpivot model which doesn't have the cell references.

 

[Marcol]

Oops! ... didn't notice the forum you posted in.

I don't know PowerPivot.

 

[robtop]

No problems, neither do I hence the problem!

 

[Laurent C]

I could not reproduce your results, and PP did not accept your measure.

Anyway, here is how I would do it:
Earliest Transaction Date:=CALCULATE(
MIN([DateOfTransaction])
,ALLEXCEPT(YourTable;YourTable[Product])
)