Asignación de pagos y cálculo de la edad del departamento

[PhilMeacham]

Tengo los siguientes datos y estoy tratando de calcular la edad de la deuda. Hay ony la tabla y no hay conciliación de cargo para el pago, así que está bien asumir que cualquier pago se asigna al pago más antiguo.

ID de cliente	TransactionDate	Tipo de pago	Cantidad	Edad
1	01/03/2020	Cargo	200,00 euros	65.00
1	01/04/2020	Cargo	300,00 euros	34.00
2	01/02/2020	Cargo	300,00 euros	94.00
2	01/03/2020	Cargo	500,00 euros	65.00
2	01/04/2020	Cargo	1.000,00 euros	34.00
1	01/04/2020	Pago	-200.00 euros	34.00
1	01/04/2020	Pago	-50,00 euros	34.00
2	01/05/2020	Pago	-400,00 euros	4.00

A partir de estos datos quiero poder calcular la edad de la deuda. Si hay un pago que se divide en 2 cargos, el cargo más antiguo se asigna primero y el resto pasa al siguiente cargo.

De la tabla anterior, me gustaría tener una tabla que se ve como por debajo:

Cliente	0 - 30	30 - 60	60 - 90	90+
1		250,00 euros
2		1.000,00 euros	400,00 euros

Si alguien puede ayudarme con esto, sería muy apaciguar.

¡Gracias de antemano!

[v-kelly-msft · Accepted solution]

Hola @PhilMeacham ,

Modifique la medida como se indica a continuación:

Measure = 
var _table1=CALCULATETABLE(FILTER(ADDCOLUMNS('Table',"a",CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[TransactionDate]<=MAX('Table'[TransactionDate])&&'Table'[Payment Type]="Charge")),"b",ABS(CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[Payment Type]="Payment"))),
"c",CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[TransactionDate]<=EARLIER('Table'[TransactionDate])&&'Table'[Payment Type]="Charge"))),[Payment Type] = "Charge"&&[b]<=[c]))
 
 
 
return
SUMX ( _table1, IF ( [c]-[b]<[Amount], [c] - [b],[Amount] ) )+0

Y verás:

Para el archivo .pbix actualizado, haga clic aquí.

Saludos
Kelly

¿Respondí a tu pregunta? ¡Marca mi puesto como solución!

[Syndicate_Admin]

@v-kelly-msft muchas gracias por su ayuda con esto.

Sin embargo, todavía estoy luchando para que funcione, pero debido a la memoria y el tiempo de tiempo de salida.

He estado tratando de hacer que funcione simplificando y trabajando en el total cumilative.

El primer paso que he tomado es ignorar si se trata de un pago o transacción y si la cantidad es > 0, es decir, es un pago, entonces simplemente establezca la fecha en 1st Jan 2000 (antes de la transacción más temprana) de esta manera el total acumulado a medida que se mueve el empate se deducirá del bote de pagos que se han realizado a lo largo del tiempo. Utilicé una consulta muy simple para crear la fecha de la transacción a continuación.

Fecha de caducidad = IF('Transacción'[Importe]>0,FECHA(2000,1,1),'Transacción'[Fecha_TFecha])

Lo que ahora estoy luchando con es cómo ahora puedo encontrar el total cumilitave por categoría de edad por cliente.

¿Tiene sentido? Realmente aprecio su ayuda hasta la fecha!

[v-kelly-msft]

Hola @PhilMeacham ,

Vaya al editor de consultas >agregar columna>columna de índice;

A continuación, cree 2 columnas como se muestra a continuación:

Age period = IF('Table'[Age]>=0&&'Table'[Age]<=30,"0 - 30",
IF('Table'[Age]>30 &&'Table'[Age]<=60,"30 - 60",
IF('Table'[Age]>60 &&'Table'[Age]<=90,"60 - 90",
IF('Table'[Age]>=90,"90+",BLANK()))))

Column 2 = 
var _jumpprevalue=MINX(FILTER('Table','Table'[Column]=EARLIER('Table'[Column])&&'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Payment Type]<>EARLIER('Table'[Payment Type])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Index])

Return
_jumpprevalue

Por último, cree una medida como se indica a continuación:

Measure 2 = 
var _jump= IF(MAX('Table'[Column 2])<>BLANK(),MINX(FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Age period]=MAX('Table'[Age period])&&'Table'[Payment Type]=MAX('Table'[Payment Type])&&'Table'[Index]<MAX('Table'[Index])),'Table'[Index]),0)
var _subtotal=SUMX(FILTER(ALL('Table'),'Table'[Age period]=MAX('Table'[Age period])&&'Table'[Customer ID]=MAX('Table'[Customer ID])),'Table'[Amount])
Return
IF(MAX('Table'[Column 2])=BLANK(),_subtotal,_subtotal-CALCULATE(MAX('Table'[Amount]),'Table'[Index]=_jump)
)

Crea una matriz y verás:

Para el archivo .pbix relacionado, haga clic aquí.

Saludos
Kelly

¿Respondí a tu pregunta? ¡Marca mi puesto como solución!

[PhilMeacham]

Hola @v-kelly-msft gracias por esto. No está del todo bien, ya que esto parecería que el cliente 1 tenía un saldo total de 450 libras esterlinas debido cuando sólo tendrían 250 libras esterlinas y estaría en el intervalo de fechas 30 - 60.

Los pagos de 200 y 50 euros que se encuentran en el rango de 30 a 60 euros deben asignarse a 200 euros que se encuentran en el intervalo de 60 a 90 días y, a continuación, los 50 euros restantes asignados a los 300 euros en el intervalo de 30 -60 días, lo que resulta en sólo 250 euros adeudados en el grupo de 30 a 60.

¿Tiene sentido?

Me preguntaba si podría hacerse utilizando un total acumulado basado en el ID del cliente y luego de alguna manera utilizar el valor máximo en el grupo de edad?

[v-kelly-msft]

Hola @PhilMeacham ,

Según su instrucción suplementaria, para ID 1,30-60 es 250, 60-90 es 200..right? Si es así, ¿dónde está el error en mi resultado?

Saludos
Kelly

¿Respondí a tu pregunta? ¡Marca mi puesto como solución!

[PhilMeacham]

Hola @v-kelly-msft gracias agin por responder y apoogies por la confusión, voy a tratar de darle un poco más de contexto.

Si nos fijamos en el cliente 1, tienen:

2 cargos y 2 pagos.

El cargo más antiguo es de 200 y 65 días, el otro cargo es de 300 y 34 días.

El primer pago de 200 euros debe ser el cargo más antiguo dejando los 300 euros que tiene 34 días. A continuación, el segundo pago de 50 euros debe ser puesto de nuevo el nuevo cargo más antiguo, es decir, el 300 euros que tiene 34 días de edad, lo que resulta en que la deuda es de 250 libras esterlinas.

Por lo tanto, la única deuda que quedaría para el Cliente 1 sería la de 250 libras esterlinas en la categoría 30 - 60. No debería haber ninguna deuda que sea de 60 a 90 días.

¿Eso tiene un poco más de sentido ahora?

¡Muchas gracias por su ayuda hasta ahora!