Problem with comparing row values in the same column

[ngulminelli]

Hi guys I have some this issue when trying to compare the current price with the ones in the past.

What I would like is to have the percentage change between the last precio_unitario paid and the other precio_unitario that the same description was paid in the pass. Then I have in the report a slicer to select the Descrption and with a matrix, have the Description, date, precio_unitario for each purchase and besides the % paid with the inmmediate below.

Hope is clear enough!

I have the following table:

fecha	Proveedor	Descripcion	Quantity	Price	precio_unitario
08-04-19 00:00	PROVEEDOR 2	Access Insulina x 100	2	8710.6	$4,355.30
01-04-19 00:00	PROVEEDOR 2	Access Insulina x 100	2	8594.2	$4,297.10
22-03-19 00:00	PROVEEDOR 2	Access Insulina x 100	1	4074	$4,074.00
11-03-19 00:00	PROVEEDOR 2	Access Insulina x 100	2	8186.8	$4,093.40
14-02-19 00:00	PROVEEDOR 2	Access Insulina x 100	2	7585.4	$3,792.70
05-02-19 00:00	PROVEEDOR 2	Access Insulina x 100	2	7391.4	$3,695.70
23-01-19 00:00	PROVEEDOR 2	Access Insulina x 100	3	11174.4	$3,724.80
14-01-19 00:00	PROVEEDOR 2	Access Insulina x 100	1	3666.6	$3,666.60
07-01-19 00:00	PROVEEDOR 2	Access Insulina x 100	1	3715.1	$3,715.10

[TeigeGao · Accepted solution]

Hi @ngulminelli ,

According to your description, my understanding is that you want to calculate the  % paid between he last precio_unitario paid and the other precio_unitario that the same description was paid in the pass. In another word, it is precio_unitario of the latest fecha / precio_unitario of other rows in the same Descripcion.

We can use the following DAX query:

Measure = var thelastone = CALCULATE(MIN(Table1[precio_unitario]),FILTER(ALL(Table1),Table1[fecha] = MAX(Table1[fecha])))
return DIVIDE(thelastone,SUM(Table1[precio_unitario]) - thelastone)

Best Regards,

Teige

[ngulminelli]

Hi Teige that´s exactly wright,  it worked fine! thank you very much!

[ngulminelli]

Hi @TeigeGao that solved part of my problem, but what I was really trying to solved is to calculate the % difference between price on index 1 from index 2, then from index 2 % difference from index 3, and so on....

is it posible? I managed to get the last price, but can figure out how to iterate....

Date	Price	Index
01-04-19 00:00	$2,493.20	1
22-03-19 00:00	$2,363.76	2
14-02-19 00:00	$2,200.55	3
30-01-19 00:00	$2,172.41	4
14-01-19 00:00	$2,127.38	5
07-01-19 00:00	$2,155.52	6
23-11-18 00:00	$2,104.87	7
07-11-18 00:00	$2,048.59	8
19-10-18 00:00	$2,110.50	9
08-10-18 00:00	$2,178.04	10
17-09-18 00:00	$2,273.71	11
22-08-18 00:00	$1,029.92	12