Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 edad | Orden de clasificación | Min | Máximo |
Total | 1 | 0 | 99999 |
No se debe | 2 | 0 | 0 |
Total atrasado | 3 | 1 | 99999 |
Atrasado (1-30) | 4 | 1 | 30 |
Atrasado (31-90) | 5 | 31 | 90 |
Atrasado (91-99999) | 6 | 91 | 99999 |
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
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:
Desafortunadamente, la publicación del informe en el servicio PowerBI mostraría el error "Visual ha excedido los recursos disponibles":
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
Solved! Go to 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:
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 )
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:
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.
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:
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 )
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.
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 )
@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