Realizar operaciones en una tabla en función de las fechas de las filas de otra tabla

[noncek]

¡Hola!

Actualmente estoy migrando una solución basada en Excel a Power BI y he encontrado un pepinillo que me cuesta pasar.

Tengo una tabla con presupuestos de pedido para algunos productos. Cada producto tiene algunos componentes, sin embargo, su número difiere de un producto a otro (puede ser entre 1 y hasta 30 componentes por producto). Además, cada producto tiene muchas cotizaciones diferentes, basadas en la fecha y pueden estar en cualquier fecha (este no es un caso de un nuevo lote de presupuestos para todos los productos que se actualizan, por ejemplo, cada 1 de enero).

Esta tabla tiene un aspecto similar a eso (y tiene más de 200k+ filas en total):

Procuct	Fecha de cotización	Componente	Costo compensado
P1	2018-01-01	C1	10
P1	2018-01-01	C2	15
P1	2018-01-01	C3	10
P1	2019-01-01	C1	11
P1	2019-01-01	C2	13
P1	2019-01-01	C3	10
P2	2018-03-01	C4	2
P2	2018-03-01	C5	9
P2	2020-01-01	C4	3
P2	2020-01-01	C5	10
P3	2020-01-01	C6	100
P3	2020-01-15	C6	103
P4	2016-05-30	C1	8
P4	2016-05-30	C2	12
P4	2016-05-30	C7	4
P4	2019-01-01	C1	11
P4	2019-01-01	C2	13
P4	2019-01-01	C7	5
P5	2001-01-01	C8	10

Así, por ejemplo, a partir de 2018-01-01, el costo de P1 es de 35 (10+15+10), y luego de 2019-01-01 hasta ahora es 34 (11+13+10).

Para P2 el costo es 11 (2+9) entre 2018-03-01 y 2020-01-01 y luego se eleva a 13 (3+10). No hubo ninguna cotización para P2 en 2019.

Para P5 sólo hay una cita general de 2001 y no cambia en absoluto.

También tengo mesa con volúmenes para cada producto que se ve así:

Procuct	Mes	Cantidad
P1	2018-01-01	100
P1	2018-02-01	120
P1	2018-03-01	150
	…
P1	2020-05-01	140
P2	2018-01-01	35
P2	2018-02-01	40
P2	2018-03-01	30
	…
P2	2020-05-01	50
…

Lo que tengo que hacer es obtener el gasto en cada producto por mes (y obviamente hacer que esto se analice más a fondo por años, proveedores, categorías, etc.). También necesito ver el gasto en componentes en sí mismos (ya que pueden aparecer en más de un producto).

Creo que necesito crear algún tipo de función comparando las fechas de presupuesto en una tabla con las fechas de volumen en la otra, pero la lucha para incluso comenzar en este 😕

Cualquier sugerencia, puntero y ayuda muy apreciada.

[MFelix]

Hola @noncek ,

Lo que necesita crear es un modelo de esquema de estrella(https://docs.microsoft.com/en-us/power-bi/guidance/star-schema) por lo que, en consecuencia, para que la configuración necesita tener una tabla de calendario y una tabla de productos.

Active el vínculo para crear una tabla de calendario (también hay otras basadas en la consulta de energía). A continuación, puede hacer sus cálculos basados en fechas y también en productos.

En adjuntar archivo PBIX es una presentación muy simple necesita ser estructura por lo que los cálculos le dan los totales entre períodos, pero por favor sólo tiene que comprobar si los valores para las fechas de specif son correctos si sí puedo ver las medidas para las fechas entre períodos.

Por favor, dime si esto puede ayudar.

[noncek]

Hola @MFelix ,

gracias por su respuesta y ayuda!

Ya tenía tabla de calendario y esquema de datos en mi modelo, sin embargo, veo que no estaba conectado correctamente. Fui con tu propuesta.

En cuanto a la pbix adjunta, muestra los valores esperados para los períodos en los que hay presupuestos. Si pudieras ayudar a llenar las lagunas de los períodos entre citas, estaría muy agradecido.

No puedo entender, sin embargo, ¿de dónde vienen los totales en éste?

Mientras que el total para un año específico tiene sentido, los totales de filas y columnas se mueven mi mente...

De todos modos, gracias de nuevo por su ayuda!

[MFelix]

Hola @noncek ,

Sólo unas pocas preguntas que aparecen en el cálculo que estaba haciendo y pueden cambiar la forma en que necesitamos abordar el problema:

• En el producto P3 tiene Cotizaciones que tiene el mismo componente con dos fechas diferentes es esto correcto?
• ¿Puede tener varios presupuestos para diferentes componentes en fechas diferentes dentro del mismo mes si es así, ¿cuál es la fecha que se tiene en cuenta?

Sólo para aclarar y obtener los cálculos correctos.

[noncek]

Hola @MFelix

Para ambas preguntas la respuesta es positiva.

Puede haber casos en los que un producto tenga más de un presupuesto durante un mes. Puede haber alos una situación cuando en un mes cerain hay una nueva cita (o un par de citas) para sólo uno de los componentes para el producto, mientras que los otros no tienen ninguna nueva cita (por lo tanto, no hay cambio).

Por lo general, la lógica debe ser que para calcular el costo del producto (suma de costes si sus componentes) en cualquier momento, debemos tomar el último presupuesto disponible para cada componente.

Así que en el ejemplo el producto P3 cuesta 100 entre 2020-01-01 y 2020-01-14 y a partir de 2020-01-15 los cambios de coste a 103 (hasta que aparezca un nuevo presupuesto para el componente C6 - en este caso concreto sólo tiene un componente).

El archivo de volúmenes es granular de hasta meses, por lo que el modelo asume que los volúmenes vienen siempre el primer día del mes. No puse los volúmenes para P3 en mi ejemplo, pero suponiendo que eran 1000 pcs cada mes, el gasto en P3 en enero de 2020 sería 100.000, febrero 2020 sería 103.000 y todos los meses siguientes en 103.000.

Espero que esto responda a su pregunta.

Y una vez más gracias por su ayuda y permanecer en el caso!

[MFelix]

Hola @noncek ,

Siento no haberte respondido todavía, sin embargo estoy haciendo un poco de trainig y es difícil conseguir el tiempo.

Te pondré en contacto con un gran datanaut.

@parry2k puede llenar en mi nombre esta pregunta? He hecho un archivo smal, pero no dude en obtener una solución diferente y mejor.

[parry2k]

Gracias @MFelix seguramente puedo ayudar con esto.

@noncek Será mucho más fácil si coloca datos de ejemplo en el archivo de Excel y los comparte aquí y también una hoja coloca el resultado esperado, me ayudará a crear la solución en Power BI y enviarla de vuelta a usted de lo contrario habrá una gran cantidad de ida y vuelta.

Salud

P

[noncek]

Gracias @MFelix por la referencia y gracias @parry2k por tomar interés!

Como miembro regular de la comunidad no parece tener la opción de adjuntar un archivo directamente aquí, pero por favor encuentre un enlace de transferencia a los xls:

https://wetransfer.com/downloads/515315cc8889932359751c914a68084020200625113807/bcf2da8976af55f1aa91...

¡Que tengas un gran día!

K