Total de facturas pendientes a partir de la fecha seleccionada

queridos todos

Necesito su ayuda para crear algunas medidas como se muestra a continuación. ¿Puedes ayudarme?

He adjuntado los datos para su referencia. De forma anticipada, muchas gracias por su ayuda.

1) Si la fecha de la factura y la fecha del recibo de efectivo es menor o igual a la fecha seleccionada

Y

Importe de la factura si se paga en su totalidad

Resultado

facturas pagadas

2) Si la factura no se paga o se paga parcialmente a partir de la fecha seleccionada

Resultado

Suma del total de facturas pendientes a la fecha seleccionada (*Para las facturas pagadas parcialmente, solo se debe calcular el saldo a la fecha seleccionada)

3) Monto total de la factura en o antes de la fecha seleccionada

4) Importe total de la factura después de la fecha seleccionada

5) Facturas sin pagar a partir de la fecha seleccionada (pendientes)

Descargar PBIX

Hola @gauravnarchal,

Consulte la siguiente medida:

Paid Invoices or Outstanding Invoices 2 =
VAR SelectedDate =
    MAX ( 'Calendar'[Date] )
VAR ReceiptDate =
    CALCULATE (
        MAX ( Cash_receipt[Receipt_Date] ),
        FILTER (
            Cash_receipt,
            Cash_receipt[invoice_id] = MAX ( ARInvoices[invoice_id] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( ARInvoices[invoice_id] ),
        IF (
            MAX ( ARInvoices[invoice_date] ) <= SelectedDate
                && ReceiptDate <= SelectedDate
                && NOT ( ISBLANK ( ReceiptDate ) ),
            BLANK (),
            CALCULATE (
                SUM ( ARInvoices[InvoiceAmount] ),
                FILTER (
                    ARInvoices,
                    ARInvoices[invoice_date] <= SelectedDate
                        && ARInvoices[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
                )
            )
                - CALCULATE (
                    SUM ( Cash_receipt[ReceiptAmount] ),
                    FILTER (
                        Cash_receipt,
                        Cash_receipt[Receipt_Date] <= SelectedDate
                            && Cash_receipt[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
                    )
                )
        ),
        [Unpaid Invoices as of Selected Date (Outstanding)]
    )

Si esta publicación le ayuda, considere aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Atentamente,

dai demon

Hola @gauravnarchal,

Consulte la siguiente medida para su para 1) y 2):

Paid Invoices or Outstanding Invoices =
VAR SelectedDate =
    MAX ( 'Calendar'[Date] )
VAR ReceiptDate =
    CALCULATE (
        MAX ( Cash_receipt[Receipt_Date] ),
        FILTER (
            Cash_receipt,
            Cash_receipt[invoice_id] = MAX ( ARInvoices[invoice_id] )
        )
    )
RETURN
    IF (
        MAX ( ARInvoices[invoice_date] ) <= SelectedDate
            && ReceiptDate <= SelectedDate
            && NOT ( ISBLANK ( ReceiptDate ) ),
        "Paid Invoices",
        CALCULATE (
            SUM ( ARInvoices[InvoiceAmount] ),
            FILTER (
                ARInvoices,
                ARInvoices[invoice_date] <= SelectedDate
                    && ARInvoices[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
            )
        )
            - CALCULATE (
                SUM ( Cash_receipt[ReceiptAmount] ),
                FILTER (
                    Cash_receipt,
                    Cash_receipt[Receipt_Date] <= SelectedDate
                        && Cash_receipt[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
                )
            )
    )

Y las siguientes medidas para 3,4)y5):

Total Invoice Amount On or Before as of Selected Date = VAR SelectedDate = MAX('Calendar'[Date]) Return CALCULATE(SUM(ARInvoices[InvoiceAmount]),FILTER(ALL(ARInvoices),ARInvoices[invoice_date]<=SelectedDate))

Total Invoice Amount After as of Selected Date = VAR SelectedDate = MAX('Calendar'[Date]) Return CALCULATE(SUM(ARInvoices[InvoiceAmount]),FILTER(ALL(ARInvoices),ARInvoices[invoice_date]>=SelectedDate))

Unpaid Invoices as of Selected Date (Outstanding) = VAR SelectedDate = MAX('Calendar'[Date]) return CALCULATE(SUM(ARInvoices[InvoiceAmount]),FILTER(ARInvoices,ARInvoices[invoice_date]<= SelectedDate&&ARInvoices[invoice_id] IN DISTINCT(ARInvoices[invoice_id])))-CALCULATE(SUM(Cash_receipt[ReceiptAmount]),FILTER(Cash_receipt,Cash_receipt[Receipt_Date]<=SelectedDate&&Cash_receipt[invoice_id] IN DISTINCT(ARInvoices[invoice_id])))

Para obtener más información, consulte el pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcdVIyl6dB9HgV98-G…

Si esta publicación le ayuda, considere aceptarla como la solución para ayudar a otros miembros a encontrarla más rápido.

Atentamente

dai demon

@v-deddai1-msft

¿Cómo puedo filtrar/eliminar facturas pagadas de la «Medida Facturas Pagadas o Facturas Pendientes»? La tabla solo debe mostrar facturas pendientes.

Gracias por toda tu ayuda.

Hola @gauravnarchal,

Simplemente cambie la medida para que se muestre en blanco:

Paid Invoices or Outstanding Invoices =
VAR SelectedDate =
    MAX ( 'Calendar'[Date] )
VAR ReceiptDate =
    CALCULATE (
        MAX ( Cash_receipt[Receipt_Date] ),
        FILTER (
            Cash_receipt,
            Cash_receipt[invoice_id] = MAX ( ARInvoices[invoice_id] )
        )
    )
RETURN
    IF (
        MAX ( ARInvoices[invoice_date] ) <= SelectedDate
            && ReceiptDate <= SelectedDate
            && NOT ( ISBLANK ( ReceiptDate ) ),
        Blank(),
        CALCULATE (
            SUM ( ARInvoices[InvoiceAmount] ),
            FILTER (
                ARInvoices,
                ARInvoices[invoice_date] <= SelectedDate
                    && ARInvoices[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
            )
        )
            - CALCULATE (
                SUM ( Cash_receipt[ReceiptAmount] ),
                FILTER (
                    Cash_receipt,
                    Cash_receipt[Receipt_Date] <= SelectedDate
                        && Cash_receipt[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
                )
            )
    )

Si esta publicación le ayuda, considere aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Atentamente,

dai demon

Hola @v-deddai1-msft

He cambiado la medida según lo aconsejado. Ahora no veo el total cuando selecciono cualquier fecha posterior al 12 de junio.

He adjuntado el PBIX.

Gracias por la ayuda de antemano.

Gaurav

Descargar PBIX

Hola @gauravnarchal,

Consulte la siguiente medida:

Paid Invoices or Outstanding Invoices 2 =
VAR SelectedDate =
    MAX ( 'Calendar'[Date] )
VAR ReceiptDate =
    CALCULATE (
        MAX ( Cash_receipt[Receipt_Date] ),
        FILTER (
            Cash_receipt,
            Cash_receipt[invoice_id] = MAX ( ARInvoices[invoice_id] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( ARInvoices[invoice_id] ),
        IF (
            MAX ( ARInvoices[invoice_date] ) <= SelectedDate
                && ReceiptDate <= SelectedDate
                && NOT ( ISBLANK ( ReceiptDate ) ),
            BLANK (),
            CALCULATE (
                SUM ( ARInvoices[InvoiceAmount] ),
                FILTER (
                    ARInvoices,
                    ARInvoices[invoice_date] <= SelectedDate
                        && ARInvoices[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
                )
            )
                - CALCULATE (
                    SUM ( Cash_receipt[ReceiptAmount] ),
                    FILTER (
                        Cash_receipt,
                        Cash_receipt[Receipt_Date] <= SelectedDate
                            && Cash_receipt[invoice_id] IN DISTINCT ( ARInvoices[invoice_id] )
                    )
                )
        ),
        [Unpaid Invoices as of Selected Date (Outstanding)]
    )

Si esta publicación le ayuda, considere aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Atentamente,

dai demon

@v-deddai1-msft – Brillante..

¡Gracias por tu ayuda!

Entradas relacionadas

Deja un comentario Cancelar respuesta