Cálculo de Overdues para una serie temporal

[Anonymous]

Buenas tardes.

Tengo una hoja de trabajo de Excel con algunas cuentas corrientes de clientes, que presento a continuación. Quiero calcular el importe total atrasado, es decir, cuánto debería haber recibido de mis clientes que no lo hice, durante un período de series temporales (por ejemplo, 2014-2018). El objetivo es devolver un gráfico de barras con el año en el eje x y la cantidad vencida en el eje y.

He creado una columna calculada donde devuelve "A tiempo" si la fecha de vencimiento es mayor que la fecha de pago, "Retrasada" de lo contrario.

En mi ejemplo, tengo dos columnas de valor diferentes que sumar. Si El estado es "Sin embargo al pago", debe devolver Total pendiente, si Fecha de pago > Fecha de vencimiento y Fecha de pago> Fecha, debe devolver el total recibido. Por lo tanto, para un año determinado, hay 3 condiciones para devolver un valor:
- La factura debe ser "Retrasada";
- La fecha de pago debe ser superior a la fecha;
- La fecha de caducidad debe ser menor que la fecha.

Por ejemplo, en 2015 debe devolver 700+2500+4000+1200+3400 a 11800o

¿Puede alguien ayudarme a averiguar la solución?

Gracias de antemano.

[v-yiruan-msft]

Hola @Oliveira ,

Puede crear una medida como se indica a continuación:

Overdues = 
VAR _cinvdate =
    MAX ( 'Table'[Invoice Date] )
VAR _pending =
    CALCULATE (
        SUM ( 'Table'[Total Pending] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] = "Yet to Pay"
                && YEAR ( 'Table'[Invoice Date] ) <= YEAR ( _cinvdate )
        )
    )
VAR _Overdues =
    _pending
        + CALCULATE (
            SUM ( 'Table'[Invoice Total] ),
            FILTER (
                'Table',
                YEAR ( 'Table'[Invoice Date] ) = YEAR ( _cinvdate )
                    && 'Table'[Expiration Date] > 'Table'[Invoice Date]
                    && NOT ( ISBLANK ( 'Table'[Payment Date] ) )
                    && 'Table'[Payment Date] > 'Table'[Expiration Date]
            )
        )
RETURN
    _Overdues

Al comprobar la captura de pantalla anterior, el resultado final es 15050.5 no 11800(3100+700+2500+150.5+4000+1200+3400) en 2015. Porque incluye a todas las empresas excepto a la empresa B y F(su fecha de pago es menor que la fecha de vencimiento). Así que quiero saber por qué la empresa A y la empresa E no se incluyeron en su fórmula (700 + 2500 + 4000 + 1200 + 3400 - 11800 ?)? ¿Falta alguna otra condición en mi fórmula?

Saludos

Rena

[Anonymous]

Gracias por sus respuestas @Kevin_Harper y @yingyinr.

Creo que no especificé suficiente mi situación. Quiero obtener la cantidad diaria para el overdues entre un período de tiempo (2014-2018). Estaba considerando 31-12-2015 para 2015, que no es del todo correcto.

Tengo el filtro de año, mes y diario en mi informe y quiero dar el poder al usuario para jugar entre fechas en un gráfico de barras y elegir qué valor(s) quiere comprobar.

Por ejemplo, si elige 31-12-2015, el gráfico de barras debe devolver 11800o. Pero si elige el intervalo entre 29-12-2015 y 31-12-2015, quiero que el gráfico de barras, con la fecha en el eje x, devuelva 11800 euros para cada día. Aquí hay una instantánea de mis filtros:

Probé tu fórmula pero no cumple con mi petición. ¿Puedo probar algo más?

Gracias de antemano.

[v-yiruan-msft]

Hola @Oliveira ,

¿Desea que los datos del cambio visual se realicen en las segmentaciones de fecha? ¿Y podrías por favor despejar mi duda en mi post anterior?

Incluye a todas las empresas excepto la empresa B y F base en mi fórmula. por qué empresa A Y Empresa E no se incluyeron en su fórmula(700+2500+4000+1200+3400o 11800o)? ¿Falta alguna otra condición en mi fórmula?

Es mejor que si usted puede proporcionar su archivo pbix de muestra con el fin de proporcionarle una solución adecuada.

Saludos

Rena

[Anonymous]

@yingyinr gracias por su respuesta.

Me gustaría que las segmentaciones de datos cambiaran los datos que se muestran en el informe. He adjuntado una muestra de Pbix en esta respuesta.

Estaba calculando el 2015 como si fuera 31/12/2015. Esto es, estaba inferiendo que, eligiendo sólo el año, devolvería el importe para el último día del año, lo que explica el 11800 en lugar de 15050,5 euros.

Gracias.

Archivo Pbix

[v-yiruan-msft]

Hola @Oliveira ,

He actualizado la fórmula de medida [Overdues] en su archivo pbix de muestra, compruebe si eso es lo que desea.

Overdues = 
VAR _seldate = SELECTEDVALUE('Calendar'[Date])
VAR _pending =
    CALCULATE (
        SUM (Folha1[Total Pending]),
        FILTER (
            ALL (Folha1),
            Folha1[Status] = "Yet to Pay"
                && Folha1[Invoice Date] <= _seldate
        )
    )
VAR _Overdues =
    _pending +
        CALCULATE (
            SUM (Folha1[Invoice Total]),
            FILTER (
                Folha1,
                Folha1[Invoice Date]<_seldate
                    && Folha1[Expiration Date] > Folha1[Invoice Date]
                    && NOT ( ISBLANK (Folha1[Payment Date]) )
                    && Folha1[Payment Date]> Folha1[Expiration Date]
                    &&'Folha1'[Payment Date]>_seldate
                    &&'Folha1'[Expiration Date]<_seldate
            )
        )
RETURN
    _Overdues

Saludos

Rena

[Kevin_Harper]

Hola

Hay 3 situtations que usted dice que deben utilizarse para calcular su total:

1. La factura debe ser "retrasada";

¿Puede explicar más detalladamente cómo un pago es "Retrasado" o cómo se llega a estar en estado "Retrasado"?

También dices:

La fecha de pago debe ser mayor que la fecha;
La fecha de caducidad debe ser menor que la fecha.

Usted citó que le gustaría devolver 5 resultados para 2015:

700 + 2500 + 4000 + 1200 + 3400

¿Por qué el 3100 de la Compañía A no está incluido en el total que está buscando? Su fecha de pago, 14/04/2015, es mayor que la fecha de vencimiento 26/02/2015.

Además, ¿a qué fecha hace referencia aquí cuando dice "La fecha de pago debe ser mayor que la fecha" ¿Qué fecha?