Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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