Forum Discussion
Problem with comparing row values in the same column
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 |
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
3 Replies
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
Hi Teige that´s exactly wright, it worked fine! thank you very much!
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
