Variación de stock entre un periodo de fechas con Power BI

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.

El día 28/2/21 se vendieron (tipo 1) 7 unidades del producto 3. El día2/2/21 se compraron (tipo 2) 4 unidades del producto 2 …

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:

Podemos ver que partimos de un stock inferior al actual

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:

Cuando incorporamos diferentes productos podemos tener, como en este caso, menos unidades pero un inventario de mayor importe.

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.

“Si se puede dibujar, se puede hacer”

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

Te puede interesar...

Deja una respuesta

Tu dirección de correo electrónico no será publicada.