Columna en Query haga referencia a otra fila del mismo query

[gfellay]

Desde ya muchas gracias a todos por leer este mensaje y ayudarnos a los menos experimentados.

Tengo una tabla que tiene fecha, material, planta, Inventario, Entradas y Salidas. El inventario para el primer día es correcto, pero para los días siguientes está mal y quisiera hacer una nueva columna que calcule el inventario sumando el inventario del día anterior, más las entradas del día anterior menos las salidas del día anterior. Es posible hacer esto en Power BI? Dejo una imagen de como lo resolvería en Excel, pero no sé si algo similar se puede resolver en PBI.

Muchas gracias!

[Syndicate_Admin]

@Syndicate_Admin ,

¿Podría proporcionar algunas descripciones más detalladas para que pueda responder a sus preguntas lo antes posible?

Esperamos su respuesta.

Saludos
Henrio

[gfellay]

Lo que quisiera es poder replicar el cálculo de la imagen de la columna "Inventory", o sea que para el primer día que tengo información, me deje la columna "Stock", pero para los días siguientes me calcule el inventario como la suma del inventario del día anterior más las entradas del día anterior menos las salidas del día anterior (eso considerando además que en la tabla tengo varios materiales y plantas).

Muchas gracias por la respuesta

[gfellay]

Finalmente pude resolverlo. Tal vez no sea la mejor forma, pero sí funciona. Por si alguien necesita algo similar, dejo los pasos y compartiré el archivo de ejemplo.

Pasos a seguir (espero no olvidar ninguno importante):
1. Crear una tabla Calendario, tomando las fechas de la tabla que se desea tratar
2. A la tabla calendario, agregarle dos nuevas columnas:

     Rank = RANK.EQ([Date],Calendario[Date],ASC)

     Add_INV =

       VAR _Actual = [Date]

       VAR _Proximo = LOOKUPVALUE(Calendario[Date],Calendario[Rank],[Rank]-1)

       RETURN _Proximo - _Actual

3. En la tabla donde tenemos los datos, vamos a crear nuevas columnas para los inventarios intermedios, siendo la última columna creada la que tendrá el inventario 'real'.

La fórmula para estos inventarios, es:

Inv_i =

VAR _rankingFecha = RELATED(Calendario[Rank])

VAR _Dias = RELATED(Calendario[Add_INV])

VAR _StockAnt = CALCULATE(sum(DATA[Inv_i-1]), ALLEXCEPT(DATA,DATA[Plant], DATA[Material]), DATEADD(Calendario[Date],_Dias,DAY))

VAR _InputAnt = CALCULATE(sum(DATA[Input]), ALLEXCEPT(DATA,DATA[Plant], DATA[Material]), DATEADD(Calendario[Date],_Dias,DAY))

VAR _OutputAnt = CALCULATE(sum(DATA[Output]), ALLEXCEPT(DATA,DATA[Plant], DATA[Material]), DATEADD(Calendario[Date],_Dias,DAY))

RETURN SWITCH(TRUE(),

        _rankingFecha < i, [Inv_i-1],

        _rankingFecha = i, _StockAnt + _InputAnt - _OutputAnt,

        _rankingFecha > i, BLANK()

)

Lo anterior fue una especie de pseudocódigo, donde hay que ir reemplazando la "i", por la columna en cuestión, por ejemplo para el segundo inventario tendríamos la fórmula:

Inv2 =

VAR _rankingFecha = RELATED(Calendario[Rank])

VAR _Dias = RELATED(Calendario[Add_INV])

VAR _StockAnt = CALCULATE(sum(DATA[Inv1]), ALLEXCEPT(DATA,DATA[Plant], DATA[Material]), DATEADD(Calendario[Date],_Dias,DAY))

VAR _InputAnt = CALCULATE(sum(DATA[Input]), ALLEXCEPT(DATA,DATA[Plant], DATA[Material]), DATEADD(Calendario[Date],_Dias,DAY))

VAR _OutputAnt = CALCULATE(sum(DATA[Output]), ALLEXCEPT(DATA,DATA[Plant], DATA[Material]), DATEADD(Calendario[Date],_Dias,DAY))

RETURN SWITCH(TRUE(),

        _rankingFecha < 2, [Inv1],

        _rankingFecha = 2, _StockAnt + _InputAnt - _OutputAnt,

        _rankingFecha > 2, BLANK()

)

Repitiendo esto para todos los valores posibles de fecha (en mi caso 5), el inventario 5 tendrá la cantidad que estoy buscando.

Dejo los links por si a alguien le sirve para tomar la idea:

- archivo excel de base

- archivo pbix