cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Optimizar/Calcular el envejecimiento de las facturas de los clientes: superó los recursos disponibles en el servicio PowerBI

Hola expertos,

Necesito su consejo en un problema de "envejecimiento clásico".

Debido a que @AlexisOlson sorprendido en otro problema que ofrece una fórmula mucho más "corta y limpia", espero que este problema "relacionado" también pueda resolverse. 🤞

Esta vez el principal problema es calcular la "edad" de las facturas vencidas del cliente en una fecha seleccionada en función de alguna agrupación dinámica. Entre 1-30 días, 31-90 días y más de 91 días.

Así que he definido la tabla AgingGroups de la siguiente manera:

Grupo de edadOrden de clasificaciónMinMáximo
Total1099999
No se debe200
Total atrasado3199999
Atrasado (1-30)4130
Atrasado (31-90)53190
Atrasado (91-99999)69199999

Debido a que necesito toda la información en una sola matriz de tabla, tuve que definir los grupos de edad adecuados para el Total Vencido que debería ser la SUMA de los tres intervalos vencidos (1-30 + 31-90 + 91-99999).

El Not Due debe usarse para las facturas que no están (todavía) vencidas en la fecha seleccionada y el Total debe ser el Total Vencido + No Vencido.

El modelo consta de 3 tablas relacionadas (por favor ignore en este momento la tabla de factCustomerTransactions_AgeGroups como lo explicaré más adelante)

dimCustomers - que contiene los detalles del Cliente

factCustomerTransactions - contiene todas las transacciones del Cliente (facturas y pagos)

dimDate - la "tabla de calendario" que no tiene relaciones activas con la tabla de transacciones

model.jpg

Entonces, mi primer paso fue crear una medida que calcule cuántos días vence cada factura en función de la fecha seleccionada:

Customer PastDue = 
IF (
    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
        >= MAX ( dimDate[Date] )
        || ISBLANK ( MAX ( dimDate[Date] ) ),
    0,
    DATEDIFF (
        MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
        MAX ( dimDate[Date] ),
        DAY
    )
)

Luego, inspirado por algunas otras publicaciones, cree una medida que calcule el "envejecimiento" por grupo en la fecha seleccionada:

Customer Sold per Aging Grup v1 = 
    VAR vSelectedDate =
        MAX ( dimDate[Date] )
    VAR minDueDate =
        MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
    VAR sumValue =
        CALCULATE (
            SUM ( factCustomerTransactions[Amount_LCY] ),
            factCustomerTransactions[Posting_Date] <= vSelectedDate,
            FILTER (
                factCustomerTransactions,
                COUNTROWS (
                    FILTER (
                        AgingGroups,
                        [Customer PastDue] >= AgingGroups[Min]
                            && [Customer PastDue]<= AgingGroups[Max]
                    )
                ) > 0
            )
        )
    RETURN
        sumValue

Y la matriz resultante funcionará perfectamente en el escritorio de Power BI:

Aging_v1.jpg

Desafortunadamente, la publicación del informe en el servicio PowerBI mostraría el error "Visual ha excedido los recursos disponibles":

ServiceErrorMessage.jpg

Tratar de poner las dos medidas anteriores en una sola y filtrar dentro de la siguiente medida "única" no ayudó:

Customer Sold per Aging Grup v2 = 
VAR vSelectedDate =
    MAX ( dimDate[Date] )
VAR minDueDate =
    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        FILTER (
            factCustomerTransactions,
            COUNTROWS (
                FILTER (
                    AgingGroups,
                    CALCULATE (
                        IF (
                            MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
                                >= MAX ( dimDate[Date] )
                                || ISBLANK ( MAX ( dimDate[Date] ) ),
                            0,
                            DATEDIFF (
                                MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
                                MAX ( dimDate[Date] ),
                                DAY
                            )
                        )
                    ) >= AgingGroups[Min]
                        && CALCULATE (
                            IF (
                                MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
                                    >= MAX ( dimDate[Date] )
                                    || ISBLANK ( MAX ( dimDate[Date] ) ),
                                0,
                                DATEDIFF (
                                    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
                                    MAX ( dimDate[Date] ),
                                    DAY
                                )
                            )
                        ) <= AgingGroups[Max]
                )
            ) > 0
        )
    )
RETURN
    if(sumValue<-0.001 || sumValue>0.001,sumValue,BLANK())

Finalmente había sacute;sdo que funcionaba también en el servicio Power BI añadiendo la tabla factCustomerTransactions_AgeGroups que es una copia de la tabla factCustomerTransactions original, excepto que tuve que quitar todas las columnas que no estaban implicadas en el cálculo de "envejecimiento":

Customer Sold per Aging Grup v3 = 
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions_AgeGroups[Amount_LCY] ),
        factCustomerTransactions_AgeGroups[Posting_Date] <= MAX ( dimDate[Date] ),
        FILTER (
            factCustomerTransactions_AgeGroups,
            COUNTROWS (
                FILTER (
                    AgingGroups,
                    CALCULATE (
                        IF (
                            MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] )
                                >= MAX ( dimDate[Date] )
                                || ISBLANK ( MAX ( dimDate[Date] ) ),
                            0,
                            DATEDIFF (
                                MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] ),
                                MAX ( dimDate[Date] ),
                                DAY
                            )
                        )
                    ) >= AgingGroups[Min]
                        && CALCULATE (
                            IF (
                                MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] )
                                    >= MAX ( dimDate[Date] )
                                    || ISBLANK ( MAX ( dimDate[Date] ) ),
                                0,
                                DATEDIFF (
                                    MIN ( factCustomerTransactions_AgeGroups[Initial_Entry_Due_Date] ),
                                    MAX ( dimDate[Date] ),
                                    DAY
                                )
                            )
                        ) <= AgingGroups[Max]
                )
            ) > 0
        )
    )
RETURN
    if(sumValue<-0.001 || sumValue>0.001,sumValue,BLANK())

Entonces, mi pregunta es:

¿Existe alguna otra forma de calcular este envejecimiento (fórmula más corta), o es posible optimizar la medida[Customer Sold per Aging Grup],sin introducir esta copia "ligera" de la tabla de transacciones y mantener un modelo más limpio / más pequeño? También debo tener en cuenta que la tabla de operaciones del usuario no se puede "resumir" porque será necesaria para una página de "perforación" con más detalles para cada factura.

¿O este enfoque de crear la tabla "ligera" y relacionarla en una relación uno a uno con la tabla original debería usarse como una "mejor práctica"? Porque un buen efecto secundario de este método parece mejorar las otras medidas v1/v2 que de repente comienzan a funcionar en el servicio Power BI. 🤔

Si es necesario, el informe completo está disponible para los próximos 30 días aquí: DAX-CustomerAgingFormula.pbix

1 ACCEPTED SOLUTION

Sí, debería ser mucho más eficiente (la consulta debería tomar menos de 100ms donde antes estaba ~5000ms) de esta manera por lo que el Servicio no debería tener problemas.

La clasificación no se realiza en ninguna de esas columnas en particular, sino en el orden que obtendría para el total de filas:

AlexisOlson_0-1635344306198.png

Para el total de filas, las variables AgeGroupMin y AgeGroupMax están en blanco (ya que no hay un solo valor), por lo que DateWindow se convierte en

DATESBETWEEN ( vSelectedDate - BLANK(), BLANK() )

que es el mismo rango que en el caso Not Due (ya que BLANK() = 0 se evalúa como True).

Si desea que esto actúe igual que Total en su lugar, puede agregar una marca de verificación de la mayúscula y minúscula:

Customer Sold per Age Group = 
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        IF ( ISBLANK ( AgeGroupMax ), BLANK(), vSelectedDate - AgeGroupMax ),
        IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin)
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )

View solution in original post

8 REPLIES 8
Syndicate_Admin
Administrator
Administrator

Hola @amitchandak , Hola @AlexisOlson

En primer lugar, me gustaría agradecerles a ambos por el tiempo que dedicaron a mi problema.

@amitchandak - He probado su enfoque y mapeo de la "tabla independiente" con mi tabla AgingGroups y luego creo la medida así:

Customer Sold per Aging Grup v4 - amitchandak = 
SUMX (
    FILTER (
        VALUES ( factCustomerTransactions[Amount_LCY] ),
        [Customer PastDue] >= MIN ( AgingGroups[Min] )
            && [Customer PastDue] <= MAX ( AgingGroups[Max] )
    ),
    factCustomerTransactions[Amount_LCY]
)

donde el [Customer PastDue] es mi fórmula "original" publicada en la publicación inicial:

Customer PastDue = 
IF (
    MIN ( factCustomerTransactions[Initial_Entry_Due_Date] )
        >= MAX ( dimDate[Date] )
        || ISBLANK ( MAX ( dimDate[Date] ) ),
    0,
    DATEDIFF (
        MIN ( factCustomerTransactions[Initial_Entry_Due_Date] ),
        MAX ( dimDate[Date] ),
        DAY
    )
)

Probablemente no entendí completamente su ejemplo, porque esta fórmula parece que no "reacciona" en la segmentación de datos de "fecha seleccionada", que es un selector de tipo "antes".

Si pudieras ayudarme a ajustar la fórmula para tener esto en cuenta, podría funcionar.

@AlexisOlson - Me sorprende de nuevo lo corta que podría ser la fórmula, excepto el hecho de que no funciona para los casos de "Not Due". 😢

Funciona perfectamente para "contenedores" vencidos e incluso para"Total vencido",pero debido a la parte "faltante" con "No debido" también el "Gran total" (Not Due + Total Due) tampoco funciona.

¿Crees que podrías encontrar un "truco" que hiciera que Not Due to work? Porque, esto es parte de mi requisito de informe y no podría "olvidarme" de él. 😁

Solo para "recapitular" sus dos aproches, he adjuntado 2 instantáneas tomadas en 2 fechas diferentes:

Snap16.jpg

Snap30.jpg

Desafortunadamente, ninguno de los enfoques no se calculará correctamente hasta el "Total" que debería ser el mismo que el "Saldo del cliente en la fecha".

¿Alguna otra sugerencia?

Atentamente

Lucian

No necesitas un "truco" tanto como un caso especial. Simplemente reemplace vSelectedDate - AgeGroupMin con IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin):

Customer Sold per Age Group = 
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        vSelectedDate - AgeGroupMax,
        IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin)
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )


Un espacio en blanco dentro de DATESBETWEEN significa que no está acotado en ese lado y la función usa la última fecha en dimDate[Date].

¡Wow, @AlexisOlson me sorprendes de nuevo cómo con solo un pequeño truco todo funciona perfecto! 🎉

Y otro BIG WOW porque esta fórmula está funcionando en el servicio Power BI: sin mensaje de error y sin esa "tabla adicional".

Ahora me mostraste de nuevo que tengo MUCHO que aprender con respecto a DAX. 😁

Todavía tengo una pregunta más: ¿Por qué esta medida ordenará "de manera diferente" a mi "mala fórmula"?

Incluso si la matriz se ordena descendente por esta medida, parece que en lugar de ordenar sobre la columna Total se está ordenando en la columna No debido.

SortAlexis.jpg

Atentamente

Lucian

Sí, debería ser mucho más eficiente (la consulta debería tomar menos de 100ms donde antes estaba ~5000ms) de esta manera por lo que el Servicio no debería tener problemas.

La clasificación no se realiza en ninguna de esas columnas en particular, sino en el orden que obtendría para el total de filas:

AlexisOlson_0-1635344306198.png

Para el total de filas, las variables AgeGroupMin y AgeGroupMax están en blanco (ya que no hay un solo valor), por lo que DateWindow se convierte en

DATESBETWEEN ( vSelectedDate - BLANK(), BLANK() )

que es el mismo rango que en el caso Not Due (ya que BLANK() = 0 se evalúa como True).

Si desea que esto actúe igual que Total en su lugar, puede agregar una marca de verificación de la mayúscula y minúscula:

Customer Sold per Age Group = 
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        IF ( ISBLANK ( AgeGroupMax ), BLANK(), vSelectedDate - AgeGroupMax ),
        IF ( AgeGroupMin = 0, BLANK(), vSelectedDate - AgeGroupMin)
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )

View solution in original post

Hola @AlexisOlson ,

Como de costumbre, soy speachless, y ahora me doy cuenta de lo poco que me refiero a DAX 🙄

Este es el resultado esperado y no sé cómo podría agradecerles tanto 🙏

Después de semanas de cavar en la dirección equivocada, me diste la mejor y más oportuna respuesta en menos de un día... entonces, ¿qué puedo decir aparte de - RESPETO!

Atentamente

Lucian

De nada. Enseñar es la mejor manera de aprender algo de verdad y he aprendido mucho al responder a miles de desafíos de la vida real que personas como tú han compartido.

Syndicate_Admin
Administrator
Administrator

No creo que funcione para el caso Not Due, pero estructurar su medida de esta manera debería ser órdenes de magnitud más eficiente:

Customer Sold per Age Group =
VAR AgeGroupMin = SELECTEDVALUE ( AgingGroups[Min] )
VAR AgeGroupMax = SELECTEDVALUE ( AgingGroups[Max] )
VAR vSelectedDate = MAX ( dimDate[Date] )
VAR DateWindow =
    DATESBETWEEN (
        dimdate[Date],
        vSelectedDate - AgeGroupMax,
        vSelectedDate - AgeGroupMin
    )
VAR sumValue =
    CALCULATE (
        SUM ( factCustomerTransactions[Amount_LCY] ),
        factCustomerTransactions[Posting_Date] <= vSelectedDate,
        factCustomerTransactions[Initial_Entry_Due_Date] IN DateWindow
    )
RETURN
    IF ( ABS ( sumValue ) > 0.001, sumValue )
Syndicate_Admin
Administrator
Administrator

@Lucian, he hecho bucket dinámico en el blog de ejemplo. Debe tener un grupo por en la tabla factCustomerTransactions( clave principal), que solo se necesita cuando se crea una fecha basada en la fecha seleccionada( En la segmentación de datos) , Una tabla de bucket independiente

ver si este enfoque puede ayudar

Segmentación dinámica Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Segmentación dinámica, bucketing o binning: https://youtu.be/CuczXPj0N-k

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.