Most recent price in a data set

[vgr]

Hi
I was going through some sale data which had customer name, item code, item description, cost/unit, quantity and value.

Date	Customer	Item code	Item Desc	Cost/unit	quantity	value
01-02-2013	BAM	A3	three	14	370	5180
02-02-2013	CAM	A1	one	10	157	1570
03-02-2013	DAM	A2	three	14	344	4816
04-02-2013	BAM	A3	three	13	376	4888
05-02-2013	CAM	A2	three	12	194	2328
06-02-2013	DAM	A3	three	10	547	5470
07-02-2013	BAM	A3	three	5	291	1455
08-02-2013	CAM	A3	three	6	300	1800
09-02-2013	DAM	A1	one	15	91	1365
10-02-2013	BAM	A3	three	13	222	2886
11-02-2013	CAM	A3	three	11	374	4114
12-02-2013	DAM	A1	one	13	277	3601
13-02-2013	CAM	A1	one	9	434	3906

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

How do I compare the latest price per item for each customer?

 

[DRSteele]

Hi. Let's try this.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
1	Date	Customer	Item code	Item Desc	Cost per unit	Quantity	Value		last for each Customer
2	February 1, 2013	BAM	A3	three	14	370	5180		BAM	$ 13.00	February 10, 2013
3	February 2, 2013	CAM	A1	one	10	157	1570		CAM	$ 9.00	February 13, 2013
4	February 3, 2013	DAM	A2	three	14	344	4816		DAM	$ 13.00	February 12, 2013
5	February 4, 2013	BAM	A3	three	13	376	4888
6	February 5, 2013	CAM	A2	three	12	194	2328
7	February 6, 2013	DAM	A3	three	10	547	5470
8	February 7, 2013	BAM	A3	three	5	291	1455
9	February 8, 2013	CAM	A3	three	6	300	1800
10	February 9, 2013	DAM	A1	one	15	91	1365
11	February 10, 2013	BAM	A3	three	13	222	2886
12	February 11, 2013	CAM	A3	three	11	374	4114
13	February 12, 2013	DAM	A1	one	13	277	3601
14	February 13, 2013	CAM	A1	one	9	434	3906

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula J2 =INDEX($E$2:$E$14,SUMPRODUCT(MAX(($B$2:$B$14=$I2)*ROW($B$2:$B$14)))-ROW($A$2:$A$14)+1) K2 =INDEX($A$2:$A$14,SUMPRODUCT(MAX(($B$2:$B$14=$I2)*ROW($B$2:$B$14)))-ROW($A$2:$A$14)+1) J3 =INDEX($E$2:$E$14,SUMPRODUCT(MAX(($B$2:$B$14=I3)*ROW($B$2:$B$14)))-ROW($A$2:$A$14)+1) K3 =INDEX($A$2:$A$14,SUMPRODUCT(MAX(($B$2:$B$14=$I3)*ROW($B$2:$B$14)))-ROW($A$2:$A$14)+1) J4 =INDEX($E$2:$E$14,SUMPRODUCT(MAX(($B$2:$B$14=I4)*ROW($B$2:$B$14)))-ROW($A$2:$A$14)+1) K4 =INDEX($A$2:$A$14,SUMPRODUCT(MAX(($B$2:$B$14=$I4)*ROW($B$2:$B$14)))-ROW($A$2:$A$14)+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Ozeroth]

There are some DAX formulas on this page you could use as a model (LastBuyPrice and LastSellPrice):
Cumulative Total – Dax Patterns