Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Power Query FIFO Inventory Valuation - ¿recursivo?

¡Hola a todos!

Habíamos experimentado un problema con nuestra valoración de inventario en DAX, ya que obtuvimos una dependencia circular. Como DAX no es capaz de recursividad, estamos bastante seguros de resolver este problema con funciones de consulta de energía.

El problema es el siguiente:

Suponiendo que empiezo con una acción inicial: QTY 900000, Precio 0,3 y también tengo mi QTY de ventas mensual y QTY de compra y precio podría crear lo siguiente (el Excel con las fórmulas para 2 meses se adjunta)

Rendimiento de entradas
Cálculo fifo promedio mensualPrecioQtyValor
Apertura total 0,30 900.000,00 270.000,00
Compras totales 0,88 1.980,00 1.749,40
Apertura total + Compras 0,30 901.980,00 271.749,40
Inventario de apertura 0,30 899.914,40 269.974,32
Inventario de compras 0,88 1.980,00 1.749,40
Inventario de cierre 0,30 901.894,40 271.723,72
COGS desde la apertura 0,30 85,60 25,68
COGS de compras 0,88 - -
Cogs totales 0,30 85,60 25,68

Luego, en el próximo mes tendría que tomar mi fila de inventario de cierre como mi fila de apertura.

Aquí es donde se encuentra el problema, ya que tendría que hacer referencia a sí mismo mientras se define la fórmula como tal:

aperturaunitrate=

si "initialstockunitrate"=blank() entonces
inventariofromopening(monthyear-1)*openingunitrate(monthyear-1)+
inventario depurchases(mesanual-1)*purchaseunitrate(mesanual-1)
/
cierre(mes-1)

Más

"inicialstockunitrate"

También lo que habría que hacer es una agrupación por "Categoría" y la columna compliance_year. Debería comenzar en el próximo año con 0, ya que sólo tenemos una acción inicial este año.

No sé si esto realmente requerirá una función recursiva en PowerQuery, así que si usted tiene alguna otra idea de cómo resolverlo, ¡también está bien! En mi cabeza esta lógica tiene sentido, pero como tengo poca experiencia con PQ estoy luchando para codificarla allí.

Adjunté un archivo de Excel que incluye tanto el cálculo realizado en Excel como los datos de ejemplo que tendrían que ser mejorados.

gracias y saludos,

Martin

6 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Este es un escenario acumulativo, no un escenario recursivo. Las funciones recursivas van a niveles más profundos de una jerarquía (y copia de seguridad) mientras se mantiene en el mismo nivel de jerarquía. Familiarícese con la función List.Accumulate - es extremadamente potente.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Había investigado eso y entendido que se asemeja a un bucle for en la consulta de energía.

    Eso debería funcionar en teoría, pero me cuesta traducir que practique, especialmente la parte de agrupación.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Así que ahora he creado un GroupedIndex que debe funcionar para hacer bucle a través de List.Generate o List.Accumulate.

    ruhrbi_0-1617004524997.png

    Para dejar más claro lo que necesito, adjunté también una captura de pantalla reciente de los datos. Debe ser sencillo para los profesionales...

    OpeningUnitRate es en realidad la columna que necesito calcular, es sólo para una mejor lectura que ya está aquí en este caso. Se asemeja a la tarifa unitaria inicial (que obtuve durante algunos meses).
    Necesito:

    UnitRate=
    Si OpeningUnitRate<>0 entonces
    AperturaUnitRate
    Más
    InventoryFromOpening(GroupIndex-1)**UnitRate*+ (Necesito el resultado anterior de la columna que estoy calculando!)
    InventoryFromPurchases(GroupIndex-1)*PurchasePrice(GroupIndex-1) (Tengo el PurchasePrice)
    /
    ClosingStock(GroupIndex-1) (Tengo el closingStock)

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        5 years ago

        ¡Gracias, eso ayudó mucho! Sin embargo, sigue siendo un problema, pero creo que es fácil de solucionar:

        Mi función:

        let
    unitratefunction = (InitialUnitRate,InventoryFromOpening,InventoryFromPurchases,PurchaseUR,ClosingStock,Counter,Index) as number=>

    let

    UR = if InitialUnitRate <> 0 then InitialUnitRate else 
        ((InventoryFromOpening{Counter-1}*@unitratefunction(InitialUnitRate,InventoryFromOpening,InventoryFromPurchases,PurchaseUR,ClosingStock,Counter-1,Index))+
        InventoryFromPurchases{Counter-1}*PurchaseUR)/ClosingStock,
  
    Return = UR

    in

    Return

in
    unitratefunction

        Mi columna agregada que llama a la función:

            UR=Table.AddColumn(#"Added Custom12", "UR", each unitratefunction([OpeningUnitRate],[InventoryFromOpening],[InventoryFromPurchases],[PurchasedValueCumu]/[PurchasedTicketsCumu],[ClosingStock],1,[Index]))
in
    UR

        A continuación, obtendré el siguiente error en aquellos elementos donde initialunitrate es 0:

        An error occurred in the ‘unitratefunction’ query. Expression.Error: We cannot convert the value 2993376.231 to type List.
Details:
    Value=2993376,231
    Type=[Type]

        ¿Alguna idea? No quiero una lista, sólo quiero el resultado como un número