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.
Buenas tardes a todos,
1.- Tengo una primera tabla que es lo programado a ventas, y una 2da tabla que es lo real vendido.
2.-La cantidad programada a ventas esta delimitada por una fecha de inicio y una fecha de fin, por lo que para un mismo producto en diferentes periodos existen diferentes cantidades programadas a venta:
tblPROGRAMA | |||||
ID_programa | id_Instalacion | id_producto | start_date | end_date | venta_programa |
1 | A | A.1 | 01-ene-22 | 30-jun-22 | 5 |
2 | A | A.1 | 01-jul-22 | 31-dic-22 | 10 |
3 | A | A.2 | 01-ene-22 | 31-dic-22 | 12 |
4 | B | B.1 | 01-ene-22 | 31-dic-22 | 5 |
5 | C | C.1 | 01-ene-22 | 31-dic-22 | 8 |
3.- En la tabal Ventas los registros tienen una fecha,
tblVENTAS | ||||
ID_consumo | fk_instalacion | fk_producto | date | venta_real |
1 | A | A.1 | 01-ene-22 | 2 |
2 | A | A.1 | 02-ene-22 | 5 |
3 | A | A.1 | 03-ene-22 | 4 |
4 | A | A.1 | 04-ene-22 | 10 |
5 | A | A.1 | 05-ene-22 | 6 |
6 | A | A.1 | 01-jul-22 | 74 |
7 | A | A.1 | 02-jul-22 | 11 |
8 | A | A.1 | 03-jul-22 | 12 |
9 | A | A.1 | 04-jul-22 | 13 |
10 | A | A.1 | 05-jul-22 | 9 |
4.- Mediante la fecha de venta quisiera obtener de la tabla Programa, el valor de venta_programa. Para lo cual seria necesario :
(Programa.start_date >= Venta.date) AND (Programa.end_date <= Venta.date)
tablaVISUALIZACIÓN
ID_consumo | fk_instalacion | fk_producto | date | venta_real | venta_programa |
1 | A | A.1 | 01-ene-22 | 2 | 5 |
2 | A | A.1 | 02-ene-22 | 5 | 5 |
3 | A | A.1 | 03-ene-22 | 4 | 5 |
4 | A | A.1 | 04-ene-22 | 10 | 5 |
5 | A | A.1 | 05-ene-22 | 6 | 5 |
6 | A | A.1 | 01-jul-22 | 74 | 10 |
7 | A | A.1 | 02-jul-22 | 11 | 10 |
8 | A | A.1 | 03-jul-22 | 12 | 10 |
9 | A | A.1 | 04-jul-22 | 13 | 10 |
10 | A | A.1 | 05-jul-22 | 9 | 10 |
5.- Intente utilizar:
Solved! Go to Solution.
Estaban invertidas las tablas, muchas gracias por el apoyo!
Hi, @Cipriano
You can try the following methods.
Column =
CALCULATE (
MAX ( tblPROGRAMA[venta_programa] ),
FILTER (
tblPROGRAMA,
[id_producto] = EARLIER ( tblVENTAS[fk_producto] )
&& [start_date] = EARLIER ( tblVENTAS[date] )
)
)
venta_programa = MAXX(FILTER(tblVENTAS,[date]<=EARLIER(tblVENTAS[date])),[Column])
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hola, la solución funciona de forma parcial, ya que al manejar mas instalaciones y productos deja de funcionar.
Tabla Programa:
PROGRAMA | |||||
ID_programa | id_Instalacion | id_producto | start_date | end_date | venta_programa |
1 | A | A.1 | 01-ene-22 | 30-jun-22 | 5 |
2 | A | A.1 | 01-jul-22 | 31-dic-22 | 10 |
3 | A | A.2 | 01-ene-22 | 31-dic-22 | 12 |
4 | B | B.1 | 01-ene-22 | 31-dic-22 | 5 |
5 | C | C.1 | 01-ene-22 | 31-dic-22 | 8 |
Tabla Ventas:
VENTAS | ||||
ID_consumo | fk_instalacion | fk_producto | date | venta_real |
1 | A | A.1 | 01/01/2022 | 2 |
2 | A | A.1 | 02/01/2022 | 5 |
3 | A | A.1 | 03/01/2022 | 4 |
4 | A | A.1 | 04/01/2022 | 10 |
5 | A | A.1 | 05/01/2022 | 6 |
6 | A | A.1 | 01/07/2022 | 74 |
7 | A | A.1 | 02/07/2022 | 11 |
8 | A | A.1 | 03/07/2022 | 12 |
9 | A | A.1 | 04/07/2022 | 13 |
10 | A | A.1 | 05/07/2022 | 9 |
11 | A | A.2 | 01/01/2022 | 6 |
12 | B | B.1 | 02/01/2022 | 74 |
13 | C | C.1 | 03/01/2022 | 11 |
14 | A | A.2 | 04/01/2022 | 6 |
15 | B | B.1 | 05/01/2022 | 74 |
16 | C | C.1 | 05/07/2022 | 11 |
Resultado esperado:
VENTAS | |||||
ID_consumo | fk_instalacion | fk_producto | date | venta_real | venta_programa |
1 | A | A.1 | 01/01/2022 | 2 | 5 |
2 | A | A.1 | 02/01/2022 | 5 | 5 |
3 | A | A.1 | 03/01/2022 | 4 | 5 |
4 | A | A.1 | 04/01/2022 | 10 | 5 |
5 | A | A.1 | 05/01/2022 | 6 | 5 |
6 | A | A.1 | 01/07/2022 | 74 | 10 |
7 | A | A.1 | 02/07/2022 | 11 | 10 |
8 | A | A.1 | 03/07/2022 | 12 | 10 |
9 | A | A.1 | 04/07/2022 | 13 | 10 |
10 | A | A.1 | 05/07/2022 | 9 | 10 |
11 | A | A.2 | 01/01/2022 | 6 | 12 |
12 | B | B.1 | 02/01/2022 | 74 | 5 |
13 | C | C.1 | 03/01/2022 | 11 | 8 |
14 | A | A.2 | 04/01/2022 | 6 | 12 |
15 | B | B.1 | 05/01/2022 | 74 | 5 |
16 | C | C.1 | 05/07/2022 | 11 | 8 |
Anexo el archivo trabajado: https://drive.google.com/file/d/1kE_0SFtNiD_zWG_IbwgPRS_MANhS1sYG/view?usp=sharing
@Cipriano ,A new column like
sumx(filter(Ventas, Ventas[fk_instalacion] = Programa[id_Instalacion] && Programa[id_producto]= Ventas[fk_producto] && Ventas[date]>= Programa[start_date] && Ventas[date]<= Programa[end_date] ), Ventas[venta_programa] )
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
Estaban invertidas las tablas, muchas gracias por el apoyo!
Hello @amitchandak, when entering the code, it marks a reference error on the Program table, I attach the file: https://drive.google.com/file/d/1sk1yClyp5dPLZabpiY3nMwxsyoShv-ss/view?usp=sharing
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |