Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Agregar una medida para resumir los importes de pago vencidos

hola

Bastante nuevo en Power BI y lucha con una consulta DAX.

Supongamos que tengo el siguiente modelo:

Gooner_chef_0-1627953199720.png

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

1 ACCEPTED 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

yingyinr_0-1628822287199.png

Saludos

View solution in original post

9 REPLIES 9
Syndicate_Admin
Administrator
Administrator

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.

yingyinr_0-1628147798911.png

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 

@yingyinr

Estos son algunos datos de ejemplo:

FACT_PAYMENT:

Gooner_chef_0-1628175191725.png

DIM_DATE:

Gooner_chef_1-1628175220867.png

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

yingyinr_0-1628822287199.png

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:

DateRecIDGREGORIANDATE
Dic 29 2019 12:00AM-0-FiscalCalendar12/29/2019
Dic 29 2019 12:00AM-1-FiscalCalendar12/29/2019
Dic 30 2019 12:00AM-1-FiscalCalendar12/30/2019
Dic 31 2019 12:00AM-1-FiscalCalendar12/31/2019
Ene 1 2020 12:00AM-1-FiscalCalendar1/1/2020
Ene 2 2020 12:00AM-1-FiscalCalendar1/2/2020
Ene 3 2020 12:00AM-1-FiscalCalendar1/3/2020
Ene 4 2020 12:00AM-1-FiscalCalendar1/4/2020
Ene 5 2020 12:00AM-1-FiscalCalendar1/5/2020
Ene 6 2020 12:00AM-1-FiscalCalendar1/6/2020
Ene 7 2020 12:00AM-1-FiscalCalendar1/7/2020
Ene 8 2020 12:00AM-1-FiscalCalendar1/8/2020
Ene 9 2020 12:00AM-1-FiscalCalendar1/9/2020
Ene 10 2020 12:00AM-1-FiscalCalendar1/10/2020
Ene 11 2020 12:00AM-1-FiscalCalendar1/11/2020
Ene 12 2020 12:00AM-1-FiscalCalendar1/12/2020
Ene 13 2020 12:00AM-1-FiscalCalendar1/13/2020
Ene 14 2020 12:00AM-1-FiscalCalendar1/14/2020
Ene 15 2020 12:00AM-1-FiscalCalendar1/15/2020
Ene 16 2020 12:00AM-1-FiscalCalendar1/16/2020
Ene 17 2020 12:00AM-1-FiscalCalendar1/17/2020
Ene 18 2020 12:00AM-1-FiscalCalendar1/18/2020
Ene 19 2020 12:00AM-1-FiscalCalendar1/19/2020
Ene 20 2020 12:00AM-1-FiscalCalendar1/20/2020
Ene 21 2020 12:00AM-1-FiscalCalendar1/21/2020

FACT_PAYMENT:

importeCustRecIdDueDateRecIdTransDateRecId
3155637145326Ago 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
10005637146079Ago 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
19.355637146079Ago 31 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
4205637145326Sep 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
5255637145326Oct 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
40005637145326Nov 6 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
6005637146089Nov 9 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
6005637145326Ene 11 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
-9.795637144576Mar 10 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
174.955637144576Mar 10 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
9.795637144576Mar 10 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
-9.795637144576Mar 11 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
9.795637144576Mar 11 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
19.585637145327Mar 13 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
-32.545637144576Mar 18 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
349.95637144576Mar 18 2021 12:00AM-1-FiscalCalendarMar 13 2021 12:00AM-1-FiscalCalendar
32.545637144576Mar 18 2021 12:00AM-1-FiscalCalendarMar 18 2021 12:00AM-1-FiscalCalendar
05637144576Mar 18 2021 12:00AM-1-FiscalCalendarMar 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:

DateRecIDGREGORIANDATE
Dic 29 2019 12:00AM-0-FiscalCalendar12/29/2019
Dic 29 2019 12:00AM-1-FiscalCalendar12/29/2019
Dic 30 2019 12:00AM-1-FiscalCalendar12/30/2019
Dic 31 2019 12:00AM-1-FiscalCalendar12/31/2019
Ene 1 2020 12:00AM-1-FiscalCalendar1/1/2020
Ene 2 2020 12:00AM-1-FiscalCalendar1/2/2020
Ene 3 2020 12:00AM-1-FiscalCalendar1/3/2020
Ene 4 2020 12:00AM-1-FiscalCalendar1/4/2020
Ene 5 2020 12:00AM-1-FiscalCalendar1/5/2020
Ene 6 2020 12:00AM-1-FiscalCalendar1/6/2020
Ene 7 2020 12:00AM-1-FiscalCalendar1/7/2020
Ene 8 2020 12:00AM-1-FiscalCalendar1/8/2020
Ene 9 2020 12:00AM-1-FiscalCalendar1/9/2020
Ene 10 2020 12:00AM-1-FiscalCalendar1/10/2020
Ene 11 2020 12:00AM-1-FiscalCalendar1/11/2020
Ene 12 2020 12:00AM-1-FiscalCalendar1/12/2020
Ene 13 2020 12:00AM-1-FiscalCalendar1/13/2020
Ene 14 2020 12:00AM-1-FiscalCalendar1/14/2020
Ene 15 2020 12:00AM-1-FiscalCalendar1/15/2020
Ene 16 2020 12:00AM-1-FiscalCalendar1/16/2020
Ene 17 2020 12:00AM-1-FiscalCalendar1/17/2020
Ene 18 2020 12:00AM-1-FiscalCalendar1/18/2020
Ene 19 2020 12:00AM-1-FiscalCalendar1/19/2020
Ene 20 2020 12:00AM-1-FiscalCalendar1/20/2020
Ene 21 2020 12:00AM-1-FiscalCalendar

1/21/2020

FACT_PAYMENT:

importeCustRecIdDueDateRecIdTransDateRecId
3155637145326Ago 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
10005637146079Ago 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
19.355637146079Ago 31 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
4205637145326Sep 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
5255637145326Oct 1 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
40005637145326Nov 6 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
6005637146089Nov 9 2020 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
6005637145326Ene 11 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
-9.795637144576Mar 10 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
174.955637144576Mar 10 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
9.795637144576Mar 10 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
-9.795637144576Mar 11 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
9.795637144576Mar 11 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
19.585637145327Mar 13 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
-32.545637144576Mar 18 2021 12:00AM-1-FiscalCalendarAgo 1 2020 12:00AM-1-FiscalCalendar
349.95637144576Mar 18 2021 12:00AM-1-FiscalCalendarMar 13 2021 12:00AM-1-FiscalCalendar
32.545637144576Mar 18 2021 12:00AM-1-FiscalCalendarMar 18 2021 12:00AM-1-FiscalCalendar
05637144576Mar 18 2021 12:00AM-1-FiscalCalendarMar 18 2021 12:00AM-1-FiscalCalendar

Espero que esto ayude!

mella

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors