Partiendo del stock final y de una tabla de movimientos de productos por fecha y tipo de movimiento (venta, compra, regularizaciones, transferencias…) vamos a calcular el stock inicial y el final de un periodo, dinámicamente, y su variación usando algo de modelado y DAX.
La información de partida
Tenemos un listado de productos al cual, para ampliar el análisis, le hemos añadido una columna con su Coste Medio (más adelante aclaraciones a este respecto).
Un tabla stock con el número actual de unidades de cada producto.
Una tabla de movimientos en la que tenemos el número de unidades que han salido-entrado de tienda, según el tipo de movimiento, por días. Para simplificar hemos reducido el tipo de movimientos a los siguientes: 1 para Ventas, 2 para Compras y 3 para Regularizaciones.
Originalmente esta tabla tenía las columnas resaltadas en amarillo. La columna encuadrada en rojo, no la pierdas de vista, haré referencia después a la misma.
En mi caso la tabla movimientos es resultante de “apilar” (consulta de unión) los diferentes tipos de movimiento de producto, que estaban en orígenes diferentes. Esto puedes hacerlo con Power Query, como vista almacenada en la Base de Datos, acumulando movimientos en un DataWarehouse… Hay ERP en los que hay un diario de movimientos, todo en una misma tabla, lo cual es ideal y facilita este paso.
El problema puede surgir cuando los movimientos de regularización son “totales”, no correctivos de las unidades ajustadas. O sea, supongamos que hay registradas 200 unidades y necesitamos ajustar para que resulten 220. Según ERP puede suceder que en lugar de registrar un movimiento de +20 unidades, aparezca uno de 220. Ojo con esto por que nos interesa sólo la variación…
Lo que entra, lo que sale…
Para calcular cuál era el stock de X dias atrás hemos de entender bien que:
- Vender supone salida de almacén. Si hoy tenemos 10 y hemos vendido 5, ayer teníamos 15 (en el ejemplo son movimientos tipo 1 y la columna “Uds” de la tabla anterior he hecho figurar el valor en negativo, indicando salida).
Veamos los siguientes movimientos diarios de ejemplo:
Este gráfico muestra cómo sucede. La línea naranja es el stock de “hoy” y para llegar al mismo hemos estado restando las unidades vendidas, 3 cada día, (las tipo 1, línea azul claro) al stock que había al inicio del periodo (azul oscuro):
Otro modo de verlo es con un gráfico en cascada, en el que va habiendo descenso de unidades:
- Compra supone entradas (en el ejemplo son movimientos tipo 2).
Si añadimos las compras (tipo 2), en el ejemplo, realizadas en dos días del periodo, el perfil del gráfico se modifica:
Visto como gráfico de cascada, los dias de compra el flujo neto de entradas es positivo:
- Regularizaciones pueden ser ajustes positivos o negativos (en el ejemplo son movimientos tipo 3)
- Roturas, son salidas
- Traspasos entre almacenes son salidas para uno…
- … y entradas para otro(s)
Si queremos ir hacia atrás habrá que sumar o restar el saldo neto de unidades movidas durante el periodo, independientemente del tipo, al stock actual para saber que había al principio del periodo analizado.
Las medidas a crear:
Para conseguirlo hemos de crear unas medidas, relativamente sencillas si se tiene claro el objetivo.
Stock Flujo Neto
Va a mostrar la suma (o resta) de unidades movidas durante el periodo, lo que llamaremos flujo neto. Para ello requerimos ajustar el número de unidades según tipo de movimiento, en el caso de unidades vendidas invertiremos el signo en la columna que hemos añadido. Una simple columna condicional nos servirá (la columna que hemos llamada “Uds” y sobre la que he llamado la atención previamente).
Una vez hecho esto, los propios filtros fecha de Power BI nos van a dar ese valor, como una simple suma, sin más:
Stk Flujo Neto = sum(Movimientos[uds])
El Stock actual viene de otra tabla y también es muy simple:
Uds Stock = sum(Stock[stock])
La representación gráfica de esta medida será una línea y no varía con el tiempo. Es una foto de las unidades en almacén, ahora.
Stock al inicio del período
Esta medida es muy sencilla si se entiende bien todo lo anterior. Si tenemos un filtro fechas “Desde-hasta”, queremos saber el stock que había en el momento “Desde”. Para ello hemos de sumar el flujo neto que hay desde ese día, hasta el día de hoy, que es para el que tenemos el inventario.
DATESBETWEEN no van a facilitar el periodo (más de la función en Microsoft):
Stock Ini periodo = var periodo=DATESBETWEEN(Fechas[Date], min(Fechas[Date]), BLANK()) var stk=Stock[Uds Stock] return stk-calculate(Movimientos[Stk Flujo Neto], periodo)
Datesbetween devuelve como resultado una tabla (columna) de fechas entre la fecha de inicio y fin especificadas.
Si el tercer parámetro de DATESBETWEEN es blank() permite coger el periodo desde hasta la ultima fecha de la tabla especificada en el primer parámetro.
En definitiva la suma de todos los movimientos desde el inicio periodo.
Calculate nos permite el cambio de contexto de la fecha que haya seleccionada como filtro y reemplazarlo con el de la variable DatesBetween.
Con este medida obtenemos el stock al principio del periodo (Min(fechas[Date])), y sin necesidad de un histórico de stocks.
¿Pero si queremos saber cuál era el stock final del periodo seleccionado?
Stock al final de periodo
La medida va a diferir poco de la anterior, sólo va a coger como fechas desde la fecha hasta que tengamos en el filtro (max(fecha)) hasta el final:
Stock Fin periodo = var periodo=DATESBETWEEN(Fechas[Date], max(Fechas[Date]), BLANK()) var stk=Stock[Uds Stock] return stk-calculate(Movimientos[Stk Flujo Neto], periodo)
Y de este modo podemos obtener las cifras de stock dinámicamente. En el ejemplo tenemos datos hasta el día 10/3, momento en el que había un inventario de 10 uds. Pero mostramos hasta el día 5/3, en el que se ha calculado 12 unidades:
El gráfico muestra la situación completa:
Cuando vemos datos diarios Stock Ini y Stock Fin coincidirán, pues inicio y fin son el mismo día. Por lo que para mostrar un evolutivo bastará con una de ellas.
¿Y si lo queremos valorado?
Lo ideal sería que los movimientos vinieran valorados en su coste. Pero en muchos casos no es así y muchos ERP realizan la valoración del stock como un proceso lanzado a demanda, que tiene el cuenta el método de valoración de stock utilizado.
En otros casos hay un histórico de de precios de coste por fecha. No sería complicado crear una función, pasando como parámetro el producto y fecha movimiento, para que devolviese el último precio de coste de ese producto. Buscaría el precio más reciente inferior o igual a la fecha del movimiento. Invocando la función desde la tabla movimientos tendríamos la valoración dinámica de cada movimiento.
Otra forma poco exigente, siempre que existan pocas fluctuaciones en el coste de los productos, es asignar un Precio Medio a los productos y via RELATED usarlo para valorar. Es el que he usado en el ejemplo.
Creamos una medida que sume la valoración de cada movimiento individualmente (SUMX) multiplicando unidades por el precio existente en la tabla relacionada Productos. Lo mismo para las unidades en stock:
Valor Uds Mvto = sumx(Movimientos, Movimientos[uds]*RELATED(Productos[pmedio])) Valor TStock = sumx(Stock, Stock[stock]*RELATED(Productos[pmedio]))
Y para conocer la valoración stock a inicio o fin, duplicamos las medidas anteriores, pero éstas referidas al valor de los movimientos, no a las unidades:
Valor Stock Ini Periodo = var periodo=DATESBETWEEN(Fechas[Date], min(Fechas[Date]), BLANK()) var stk=Stock[Valor TStock] return stk+calculate(Movimientos[Valor Uds Mvto], periodo)
Valor Stock Fin Periodo = var periodo=DATESBETWEEN(Fechas[Date], max(Fechas[Date]), BLANK()) var stk=Stock[Valor TStock] return stk+calculate(Movimientos[Valor Uds Mvto], periodo)
Y finalmente la variación que se ha producido de inicio a fin de periodo:
Var Valor Stock Periodos = -Movimientos[Valor Stock Ini Periodo]+ Movimientos[Valor Stock Fin Periodo]
Si lo hacemos tanto para unidades como para Importes, el resultado arroja grandes posibilidades de análisis:
Conclusión
He presentado una forma sencilla de mostrar la evolución del stock entre periodos sin disponer de una foto del cierre de stock de cada día, a partir de un recuento final y un detalle de movimientos.
No es complicado como ha podido verse, basta con pensar hacia “atrás”, en cómo “deshacer” operaciones que han llevado a la situación actual. A mi me sirvió dibujar volúmenes y hacer sumas-restas: Eso casi me llevó más tiempo que confeccionar la solución con Power BI.
Espero que te sea de ayuda.
Si quieres ver algo más de almacenes en este blog te sugiero leer Mapas personalizados con PowerBI. Analizar ubicaciones de un almacén.
Y si quieres descargar los archivos utilizados en este proyecto:
Datos en Excel y Proyecto Power BI de cálculo variación de inventario
Apasionado de las tecnologías de análisis de datos y Business Intelligence, y con larga experiencia en el uso de las mismas en empresas de todos los sectores, tamaños, y proyectos de diversa complejidad, en los cuales disfruto creando, planteando y desarrollando soluciones.
Tengo una curiosidad insaciable y demasiadas aficiones, entre ellas tocar el bajo con mi grupo.
Buenos dias santiago, estoy siguiendo este articulo, y estoy empesando con el mundo de power bi, y me encuentro armando algo asi, pero mi problematica es que no tengo tabla stock si no que mi tabla de movimientos de entra y salida, es por productos, como podria calcular el stock final.
Hola Elias, en este caso, la mejor aproximación que tienes supongo que es la suma de todas las cantidades por producto, sin fecha, que debería corresponder al stock actual. Puedes crear una tabla con dos columnas, Producto y suma mvtos, y usarla como tabla stock relacionando con el modelo.
Hola Santiago. Gracias por la respuesta. Intento aclararte el escenario. La temporada de compra la asigno manualmente en la tabla de hechos de compras (se indica en una columna para cada compra). La temporada de compra no es un atributo del producto, ya que el mismo producto lo puedo comprar en varias temporadas distintas. La temporada tampoco corresponde a unas fechas determinadas. Tengo una pequeña tabla con las temporadas de compra simplemente para tener un índice para ordenar cronológicamente las temporadas. Por ejemplo, compro 5 unidades de un producto en la temporada 1 y 10 unidades del mismo producto en la temporada 2. Paralelamente voy vendiendo dicho producto. Lo que me interesaría es, para una fecha dada en la que haya vendido 6 unidades, saber que de la temporada 1 me quedan 0 unidades y de la temporada 2 me quedan 9 unidades (como un método FIFO, pero sólo con unidades, sin valoración). Interesaría tener una matriz de Power BI con los productos en filas y las temporadas de compra en columnas, para ver el stock que queda de cada temporada. A ver si puedes orientarme! Gracias. Un saludo.
Hola Domènec, lo veo complicado con Power BI, porque en algún sitio has de guardar lo consumido de una temporada y el “saldo” o stock restante que te queda… O sea, que en algún sitio has de haber preprocesado la temporada (o temporadas) origen de una línea de venta. Lamento no poder ser de más ayuda pero, hasta donde entiendo, creo que es algo más de disponer de datos creados por un adecuado procedimiento de trabajo previo que de analítica “correctiva”. Un saludo.
Hola Santiago. He seguido con mucho interés este artículo (así como el resto del blog). Tengo un archivo de Power BI con algo muy similar al cálculo de stock que has desarrollado y quería plantearte un reto, a ver si puedes orientarme a solucionarlo o si te parece interesante para desarrollarlo en tu blog. Mi problema simplificado es el siguiente: tengo una tabla de hechos de compras y ventas de artículos con fecha. Esto me permite saber el stock diario. Las compras se clasifican por temporadas (se indica en una columna adicional en la tabla de compras). El problema es saber el stock disponible de cada temporada para cada artículo, teniendo en cuenta que las ventas empiezan por los artículos de temporadas más antiguas. Hay una tabla con el orden de las temporadas. Un artículo puede pertenecer a varias temporadas. El problema lo he resuelto parcialmente de forma poco satisfactoria, creando una medida del stock disponible de cada temporada. El inconveniente es que tengo que crear una medida concreta para cada temporada (puede ser un número grande de temporadas) y no es operativo. Mi objetivo es crear una medida que sirva para todas la temporadas y que pueda usarse, por ejemplo, en una matriz de Power Bi, colocando los artículos en filas y las temporadas en columnas, indicando como valor el stock disponible. Espero que haya quedado claro el planteamiento y puedas darme una solución. Muchas gracias.
Hola Domènec, aunque creo que está bien explicado, me surjen muchas dudas. ¿Lo que determina la temporada es el “documento” de compra?¿No debería ser un atributo del producto?. Entiendo el planteamiento para una valoración FIFO, pero para clasificar temporadas, no lo termino de ver. En todo caso, si no es un volumen de datos muy grande, trataría de resolver la asignación de temporada a la venta en Power Query, no por que se me ocurra una solución que darte al respecto, sino por que dispones de más funcionalidades para manipulaciones complicadas. Un saludo