Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hola
Bastante nuevo en Power BI y lucha con una consulta DAX.
Supongamos que tengo el siguiente modelo:
La tabla FACT_PAYMENTS contiene transacciones de pago con una fecha de vencimiento y un vínculo de cliente.
Quiero añadir una nueva medida, que para cualquier registro, resume el importe del pago que se "pasa adeudado". Esta sería una suma de todos los montos de pago [FACT_PAYMENT. AMOUNT] donde la fecha de vencimiento [DIM_DATE. GREGORIANDATE] es menor que el debido para el registro actual y el identificador de cliente [FACT_PAYMENT. CustRecId] es el mismo.
Puedo hacer esto en SQL escribiendo una subconsulta con una auto-unión, pero me gustaría evitar hacer tales cálculos en la capa de almacén.
Cualquier ayuda con esto sería muy apreciada.
Gracias de antemano,
mella
Solved! Go to Solution.
Hay @Gooner_chef,
Lo sentimos por el retraso. He creado un archivo pbix de ejemplo(ver adjunto) para usted, por favor, compruebe si eso es lo que desea.
1. Cree una medida como la siguiente para obtener la suma de la cantidad que pasa la fecha de vencimiento
AmountPassedDue =
VAR _curcust =
SELECTEDVALUE ( 'FACT_PAYMENT'[CustRecId] )
VAR _duedate =
SELECTEDVALUE ( 'DIM_DATE'[GREGORIANDATE] )
RETURN
CALCULATE (
SUM ( 'FACT_PAYMENT'[Amount] ),
FILTER ( ALLSELECTED ( 'FACT_PAYMENT' ), 'FACT_PAYMENT'[CustRecId] = _curcust ),
FILTER ( ALLSELECTED ( 'DIM_DATE' ), 'DIM_DATE'[GREGORIANDATE] < _duedate )
)
2. Crear una tabla visual aplicar la medida en ella
Saludos
Hay @Gooner_chef,
¿En qué dos campos se basan las tablas DIM_DATE y FACT_PAYMENT relaciones creadas, respectivamente? ¿Podría proporcionar algunos datos de ejemplo para estos dos cuadros (excluidos los datos confidenciales)? Puede crear una medida como se muestra a continuación para obtener la suma de la cantidad en las condiciones especiales...
Measure =
CALCULATE (
SUM ( FACT_PAYMENT[AMOUNT] ),
FILTER (
FACT_PAYMENT,
FACT_PAYMENT[xxdate] > RELATED ( DIM_DATE[GREGORIANDATE] )
&& FACT_PAYMENT[CustRecId] = SELECTEDVALUE ( FACT_PAYMENT[CustRecId] )
)
)
Saludos
Hola yingyinr,
Muchas gracias por la respuesta.
Esto se ve cerca. La relación es [FACT_PAYMENT]. [DueDateRecId] = [DIM_DATE]. [DateRecId].
El valor de fecha real se almacena en [DIM_DATE]. [GregorianDate]. Así que primero tendría que unir las dos tablas, y luego hacer la comparación de fechas.
Espero que esto tenga sentido!
Hay @Gooner_chef,
De acuerdo con la captura de pantalla en su primera publicación, ha creado dos relaciones entre la tabla DIM_DATE y FACT_PAYMENT (una está activa, la otra está inactiva). Esta relación ([FACT_PAYMENT]. [DueDateRecId] = [DIM_DATE]. [DateRecId]) ¿está activo o inactivo? Además de esta relación, ¿en qué dos columnas de cada una de estas dos tablas se basa la relación? ¿Podría compartir algunos datos de muestra de estas dos tablas(excluir datos confidenciales) y las instrucciones SQL donde utilizó para lograr la misma función con el fin de proporcionarle una solución adecuada? Gracias.
Saludos
Hay @yingyinr
Aquí está el SQL que utilicé para lograr un resultado similar:
SELECT
ct1.ACCOUNTNUM,
dim1.DateRecID,
SUM([AMOUNTCUR]) as AmountDue,
(
SELECT
SUM(AMOUNTCUR)
FROM CustTrans ct2
INNER JOIN common.dim_date dim2 ON ct2.[DUEDATE] = dim2.GREGORIANDATE
WHERE (ct1.ACCOUNTNUM = ct2.ACCOUNTNUM)
AND (ct2.DUEDATE < ct1.DUEDATE)
) As AmountPassedDue
FROM CUSTTRANS ct1
INNER JOIN common.dim_date dim1 ON ct1.[DUEDATE] = dim1.GREGORIANDATE
GROUP BY ACCOUNTNUM, dim1.DateRecID, ct1.DUEDATE
Hay @Gooner_chef,
Lo sentimos por el retraso. He creado un archivo pbix de ejemplo(ver adjunto) para usted, por favor, compruebe si eso es lo que desea.
1. Cree una medida como la siguiente para obtener la suma de la cantidad que pasa la fecha de vencimiento
AmountPassedDue =
VAR _curcust =
SELECTEDVALUE ( 'FACT_PAYMENT'[CustRecId] )
VAR _duedate =
SELECTEDVALUE ( 'DIM_DATE'[GREGORIANDATE] )
RETURN
CALCULATE (
SUM ( 'FACT_PAYMENT'[Amount] ),
FILTER ( ALLSELECTED ( 'FACT_PAYMENT' ), 'FACT_PAYMENT'[CustRecId] = _curcust ),
FILTER ( ALLSELECTED ( 'DIM_DATE' ), 'DIM_DATE'[GREGORIANDATE] < _duedate )
)
2. Crear una tabla visual aplicar la medida en ella
Saludos
¡Uau! Esto es realmente genial. ¡El uso de variables hace que esto sea muy fácil de entender! Muchas gracias por su ayuda 🙂
Hay @yingyinr
Sí, tiene razón. DIM_DATE es una dimensión de juego de roles en este caso. El FACT_PAYMENT contiene dos relaciones con DIM_DATE; TransDateRecid y DueDateRecId. Por motivos de simplicidad, puedo asegurarme de que la relación DueDate está activa.
Aquí hay un ejemplo de SQL que usé para lograr el resultado que necesito:
SELECT
ct1.ACCOUNTNUM,
dim1.DateRecID,
SUM([AMOUNTCUR]) as AmountDue,
(
SELECT
SUM(AMOUNTCUR)
FROM CustTrans ct2
INNER JOIN common.dim_date dim2 ON ct2.[DUEDATE] = dim2.GREGORIANDATE
WHERE (ct1.ACCOUNTNUM = ct2.ACCOUNTNUM)
AND (ct2.DUEDATE < ct1.DUEDATE)
) As AmountPassedDue
FROM CUSTTRANS ct1
INNER JOIN common.dim_date dim1 ON ct1.[DUEDATE] = dim1.GREGORIANDATE
GROUP BY ACCOUNTNUM, dim1.DateRecID, ct1.DUEDATE
Las tablas/columnas tienen un nombre diferente, pero el principio es el mismo.
Estos son algunos datos de ejemplo.
DIM_DATE:
DateRecID | GREGORIANDATE |
Dic 29 2019 12:00AM-0-FiscalCalendar | 12/29/2019 |
Dic 29 2019 12:00AM-1-FiscalCalendar | 12/29/2019 |
Dic 30 2019 12:00AM-1-FiscalCalendar | 12/30/2019 |
Dic 31 2019 12:00AM-1-FiscalCalendar | 12/31/2019 |
Ene 1 2020 12:00AM-1-FiscalCalendar | 1/1/2020 |
Ene 2 2020 12:00AM-1-FiscalCalendar | 1/2/2020 |
Ene 3 2020 12:00AM-1-FiscalCalendar | 1/3/2020 |
Ene 4 2020 12:00AM-1-FiscalCalendar | 1/4/2020 |
Ene 5 2020 12:00AM-1-FiscalCalendar | 1/5/2020 |
Ene 6 2020 12:00AM-1-FiscalCalendar | 1/6/2020 |
Ene 7 2020 12:00AM-1-FiscalCalendar | 1/7/2020 |
Ene 8 2020 12:00AM-1-FiscalCalendar | 1/8/2020 |
Ene 9 2020 12:00AM-1-FiscalCalendar | 1/9/2020 |
Ene 10 2020 12:00AM-1-FiscalCalendar | 1/10/2020 |
Ene 11 2020 12:00AM-1-FiscalCalendar | 1/11/2020 |
Ene 12 2020 12:00AM-1-FiscalCalendar | 1/12/2020 |
Ene 13 2020 12:00AM-1-FiscalCalendar | 1/13/2020 |
Ene 14 2020 12:00AM-1-FiscalCalendar | 1/14/2020 |
Ene 15 2020 12:00AM-1-FiscalCalendar | 1/15/2020 |
Ene 16 2020 12:00AM-1-FiscalCalendar | 1/16/2020 |
Ene 17 2020 12:00AM-1-FiscalCalendar | 1/17/2020 |
Ene 18 2020 12:00AM-1-FiscalCalendar | 1/18/2020 |
Ene 19 2020 12:00AM-1-FiscalCalendar | 1/19/2020 |
Ene 20 2020 12:00AM-1-FiscalCalendar | 1/20/2020 |
Ene 21 2020 12:00AM-1-FiscalCalendar | 1/21/2020 |
FACT_PAYMENT:
importe | CustRecId | DueDateRecId | TransDateRecId |
315 | 5637145326 | Ago 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
1000 | 5637146079 | Ago 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
19.35 | 5637146079 | Ago 31 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
420 | 5637145326 | Sep 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
525 | 5637145326 | Oct 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
4000 | 5637145326 | Nov 6 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
600 | 5637146089 | Nov 9 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
600 | 5637145326 | Ene 11 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
-9.79 | 5637144576 | Mar 10 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
174.95 | 5637144576 | Mar 10 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
9.79 | 5637144576 | Mar 10 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
-9.79 | 5637144576 | Mar 11 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
9.79 | 5637144576 | Mar 11 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
19.58 | 5637145327 | Mar 13 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
-32.54 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
349.9 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Mar 13 2021 12:00AM-1-FiscalCalendar |
32.54 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Mar 18 2021 12:00AM-1-FiscalCalendar |
0 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Mar 18 2021 12:00AM-1-FiscalCalendar |
Espero que esto ayude!
mella
Hay @yingyinr
Sí, tiene razón. La DIM_DATE es una dimensión de juego de roles en este caso. El FACT_PAYMENT tiene una relación basada en TransactionDate y DueDate. Para simplificar, podemos activar la relación DueDate.
Aquí está el SQL que utilicé:
SELECT
ct1.ACCOUNTNUM,
dim1.DateRecID,
SUM([AMOUNTCUR]) as AmountDue,
(
SELECT
SUM(AMOUNTCUR)
FROM CustTrans ct2
INNER JOIN common.dim_date dim2 ON ct2.[DUEDATE] = dim2.GREGORIANDATE
WHERE (ct1.ACCOUNTNUM = ct2.ACCOUNTNUM)
AND (ct2.DUEDATE < ct1.DUEDATE)
) As AmountPassedDue
FROM CUSTTRANS ct1
INNER JOIN common.dim_date dim1 ON ct1.[DUEDATE] = dim1.GREGORIANDATE
GROUP BY ACCOUNTNUM, dim1.DateRecID, ct1.DUEDATE
Los nombres de tabla/columna son ligeramente diferentes, pero el prinicple es el mismo.
Los datos de ejemplo son los siguientes:
DIM_DATE:
DateRecID | GREGORIANDATE |
Dic 29 2019 12:00AM-0-FiscalCalendar | 12/29/2019 |
Dic 29 2019 12:00AM-1-FiscalCalendar | 12/29/2019 |
Dic 30 2019 12:00AM-1-FiscalCalendar | 12/30/2019 |
Dic 31 2019 12:00AM-1-FiscalCalendar | 12/31/2019 |
Ene 1 2020 12:00AM-1-FiscalCalendar | 1/1/2020 |
Ene 2 2020 12:00AM-1-FiscalCalendar | 1/2/2020 |
Ene 3 2020 12:00AM-1-FiscalCalendar | 1/3/2020 |
Ene 4 2020 12:00AM-1-FiscalCalendar | 1/4/2020 |
Ene 5 2020 12:00AM-1-FiscalCalendar | 1/5/2020 |
Ene 6 2020 12:00AM-1-FiscalCalendar | 1/6/2020 |
Ene 7 2020 12:00AM-1-FiscalCalendar | 1/7/2020 |
Ene 8 2020 12:00AM-1-FiscalCalendar | 1/8/2020 |
Ene 9 2020 12:00AM-1-FiscalCalendar | 1/9/2020 |
Ene 10 2020 12:00AM-1-FiscalCalendar | 1/10/2020 |
Ene 11 2020 12:00AM-1-FiscalCalendar | 1/11/2020 |
Ene 12 2020 12:00AM-1-FiscalCalendar | 1/12/2020 |
Ene 13 2020 12:00AM-1-FiscalCalendar | 1/13/2020 |
Ene 14 2020 12:00AM-1-FiscalCalendar | 1/14/2020 |
Ene 15 2020 12:00AM-1-FiscalCalendar | 1/15/2020 |
Ene 16 2020 12:00AM-1-FiscalCalendar | 1/16/2020 |
Ene 17 2020 12:00AM-1-FiscalCalendar | 1/17/2020 |
Ene 18 2020 12:00AM-1-FiscalCalendar | 1/18/2020 |
Ene 19 2020 12:00AM-1-FiscalCalendar | 1/19/2020 |
Ene 20 2020 12:00AM-1-FiscalCalendar | 1/20/2020 |
Ene 21 2020 12:00AM-1-FiscalCalendar | 1/21/2020
|
FACT_PAYMENT:
importe | CustRecId | DueDateRecId | TransDateRecId |
315 | 5637145326 | Ago 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
1000 | 5637146079 | Ago 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
19.35 | 5637146079 | Ago 31 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
420 | 5637145326 | Sep 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
525 | 5637145326 | Oct 1 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
4000 | 5637145326 | Nov 6 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
600 | 5637146089 | Nov 9 2020 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
600 | 5637145326 | Ene 11 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
-9.79 | 5637144576 | Mar 10 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
174.95 | 5637144576 | Mar 10 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
9.79 | 5637144576 | Mar 10 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
-9.79 | 5637144576 | Mar 11 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
9.79 | 5637144576 | Mar 11 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
19.58 | 5637145327 | Mar 13 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
-32.54 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Ago 1 2020 12:00AM-1-FiscalCalendar |
349.9 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Mar 13 2021 12:00AM-1-FiscalCalendar |
32.54 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Mar 18 2021 12:00AM-1-FiscalCalendar |
0 | 5637144576 | Mar 18 2021 12:00AM-1-FiscalCalendar | Mar 18 2021 12:00AM-1-FiscalCalendar |
Espero que esto ayude!
mella