cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Cálculo de Overdues para una serie temporal

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.

Excel.PNG

6 REPLIES 6
Community Support
Community Support

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

Calculating Overdues for a time-series.JPG

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

Filters.PNG

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

Gracias de antemano.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I
Helper I

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?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.