Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |