Método de cálculo de saldos negativos acumulados

[Syndicate_Admin]

Hola amigos,

Tengo la necesidad de determinar la caída negativa acumulada en un saldo diario. Tenemos una fórmula de Excel que hace lo que queremos, pero estoy luchando por cómo convertirla en mi modelo de datos de Power BI: he estado intentando usar DAX, pero estoy abierto a usar Power Query o DAX para lograrlo.

Aquí está la fórmula de Excel que está funcionando:

Este es el modelo de datos

Y mis medidas:
Chg neto = SUMA(CashBookDailySummaryUSD[Comercio])

**pitido** Chg neto =

CALCULAR (

[Chg neto],

FILTRO (

TODO ( 'Calendario'[Fecha] ),

'Calendario'[Fecha] <= MÁXIMO ( ( 'Calendario'[Fecha] ) )

)

)

Caída acumulativa =

VAR NetChg = [Chg neto]

VAR AntNetChg = CALCULAR([Chg neto], DÍA ANTERIOR('Calendario'[Fecha]))

VAR AntCumBal = CALCULAR([**bip** Chg neto], DÍA ANTERIOR('Calendario'[Fecha]))

DEVOLUCIÓN

SI(

NetChg < 0,

SI(

AntNetChg < 0,

NetChg + AntNetChg,

NetChg

),

SI(

AntNetChg < 0,

SI(

NetChg < ABS(AntNetChg),

NetChg + AntNetChg,

NetChg

),

NetChg

)

)

Básicamente, necesito seguir acumulando la caída del saldo en negativo hasta que vuelva a ser positiva, y parece que no puedo averiguar cómo hacerlo ya que no puedo hacer referencia al valor anterior de la medida de caída acumulativa.

Gracias de antemano por cualquier ayuda.

[Syndicate_Admin]

@dusdau ,

Gracias por ponerse en contacto con el foro de la comunidad de Microsoft Fabric.

También me gustaría tomarme un momento para agradecer a @DataNinja777 , por participar activamente en el foro de la comunidad y por las soluciones que han estado compartiendo en el foro de la comunidad. Sus contribuciones marcan una diferencia real.

¿Puedo preguntarle si ha resuelto este problema? Si es así, marque la respuesta útil y acéptela como la solución. Esto será útil para que otros miembros de la comunidad que tengan problemas similares lo resuelvan más rápido.

Gracias.

[Syndicate_Admin]

@dusdau ,

La fórmula que desea crear es, de hecho, recursiva. Un cálculo recursivo es aquel en el que el resultado de la fila actual depende directamente del resultado del mismo cálculo de la fila anterior. Esta lógica autorreferencial es exactamente la razón por la que se produce un error en una columna calculada de DAX estándar, ya que conduce a un error de "dependencia circular" cuando la fórmula intenta hacer referencia a sí misma durante el proceso de cálculo.

La manera más eficaz y sólida de compilar esta lógica en Power BI es mediante Power Query, que está diseñado para controlar estas operaciones secuenciales fila por fila antes de que los datos se carguen en el modelo. Para implementar la solución, primero deberá agregar una columna de índice y, a continuación, una columna personalizada con el siguiente código M. En este código se supone que la columna de cambio neto se denomina [Net Chg]; Ajuste este nombre en la fórmula si el suyo es diferente.
Claro. Aquí está la respuesta reescrita en párrafos con el fragmento de código.

Tu intuición es correcta; La fórmula que desea crear es, de hecho, recursiva. Un cálculo recursivo es aquel en el que el resultado de la fila actual depende directamente del resultado del mismo cálculo de la fila anterior. Esta lógica autorreferencial es exactamente la razón por la que se produce un error en una columna calculada de DAX estándar, ya que conduce a un error de "dependencia circular" cuando la fórmula intenta hacer referencia a sí misma durante el proceso de cálculo.

La manera más eficaz y sólida de compilar esta lógica en Power BI es mediante Power Query, que está diseñado para controlar estas operaciones secuenciales fila por fila antes de que los datos se carguen en el modelo. Para implementar la solución, primero deberá agregar una columna de índice y, a continuación, una columna personalizada con el siguiente código M. En este código se supone que la columna de cambio neto se denomina [Net Chg]; Ajuste este nombre en la fórmula si el suyo es diferente.

// This code replicates the recursive Excel logic
// It must be run on a table sorted by date

try
    let
        CurrentNetChg = [Net Chg],
        PrevDrop = #"Changed Type"{[Index]-1}[CumulativeDrop]
    in
        if CurrentNetChg < 0 then
            if PrevDrop < 0 then CurrentNetChg + PrevDrop else CurrentNetChg
        else
            if PrevDrop < 0 then
                if CurrentNetChg < Number.Abs(PrevDrop) then CurrentNetChg + PrevDrop else CurrentNetChg
            else CurrentNetChg
otherwise
    if [Net Chg] < 0 then [Net Chg] else 0

Para usar esta solución, primero debe abrir el Editor de Power Query haciendo clic en "Transformar datos" en Power BI. Es fundamental seleccionar la consulta y ordenar la tabla por fecha en orden ascendente. Después de ordenar, agregue una columna de índice navegando a la pestaña "Agregar columna" y seleccionando "Columna de índice" (comenzando desde 0). Con el índice en su lugar, puede agregar una "Columna personalizada" y pegar el código M proporcionado en el cuadro de fórmula. La fórmula usa el índice para hacer referencia al valor calculado anteriormente en la columna [CumulativeDrop], y el try... De lo contrario, maneja correctamente el cálculo de la primera fila en la que no existen datos anteriores.

Esta lógica replica fielmente su fórmula de Excel, donde se muestra un Chg neto positivo una vez que se compensa una caída acumulativa. Si prefiere que el valor se restablezca a cero, puede cambiar el else final CurrentNetChg en el código a else 0. Aunque técnicamente es posible utilizar una solución puramente basada en DAX mediante medidas complejas con tablas virtuales, el enfoque de Power Query es mucho más claro, más fácil de mantener y, en general, más eficaz para este tipo de tarea recursiva.

Saludos

[Syndicate_Admin]

Gracias DataNinja. Lo intenté hoy y no parece estar haciendo referencia a la columna CumulativeDrop correctamente. Creo que podría deberse a que la columna no existe en el paso anterior.

dejar
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJtisQwCIavUvprCsJEjV9nGeb+11jbSCfd3Q4EfAM+Ud/4eq38xHhSI15hXcIcGHFJXecNewq1Swp12lOWh8b2JxaBHyIMWOwgMABR7mShdKIIrQsE65HWHcTiThbMM0wGqKPGAx3McLvVxfeTdzFQiaI1QJpu/1w8DTE7H5Dpp. Ac/+h1XsfImJGXj0E9NLXeM20hhpu4qj3l68QJsGRqbspR9oRpD+RRfvM98pm6pPaaCEd7LgaXVaeh0+uiYW0LDty2U8wJ/FchQgr8UKhMAyeNacP6rIJ45X12Q0mVN0jd/yQS454W75+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) en la tabla de tipos [Date = _t, #"USD Balance" = _t, Change = _t, #"Net Change" = _t, #"Cume Drop Excel" = _t]),
#"Tipo cambiado" = Tabla.TransformColumnTypes(Fuente,{{"Fecha", fecha de tipo}, {"Saldo de USD", número de tipo}, {"Cambio", número de tipo}, {"Cambio neto", número de tipo}, {"Cume Drop Excel", número de tipo}}),
#"Filas ordenadas" = Tabla.Ordenar(#"Tipo cambiado",{{"Fecha", Order.Ascending}}),
#"Índice agregado" = Tabla.AddIndexColumn(#"Filas ordenadas", "índice", 0, 1, int64.Type),
#"Agregado Personalizado" = Tabla.AgregarColumna(#"Índice Agregado", "Gota Acumulativa", cada uno // Este código replica la lógica recursiva de Excel
Debe ejecutarse en una tabla ordenada por fecha

probar
dejar
CurrentNetChg = [cambio neto],
PrevDrop = #"Índice agregado"{[Index]-1}[CumulativeDrop]
en
si CurrentNetChg < 0, entonces
if PrevDrop < 0 then CurrentNetChg + PrevDrop else CurrentNetChg
más
si PrevDrop < 0, entonces
if CurrentNetChg < Number.Abs(PrevDrop) then CurrentNetChg + PrevDrop else CurrentNetChg
else CurrentNetChg
de otra manera
si [Cambio neto] < 0, entonces [Cambio neto] else 0)
en
#"Personalizado agregado"