Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Gooner_chef
Regular Visitor

Adding a measure to summarize passed due payment amounts

‎08-02-2021 06:19 PM

Hi,

Fairly new to Power BI and struggle with a DAX query.

Suppose I have the following model:

Gooner_chef_0-1627953199720.png

The FACT_PAYMENTS table contain payment transactions with a due date and customer link.

 

I want to add a new measure, which for any record, summarizes the payment amount which is 'passed due'. This would be a sum of all payment amounts [FACT_PAYMENT.AMOUNT] where the due date [DIM_DATE.GREGORIANDATE] is less than due for the current record and the customer ID [FACT_PAYMENT.CustRecId] is the same.

 

I can do this in SQL by writing a subquery with a self-join, but I would like to avoid doing such calculations in the warehouse layer.

 

Any help with this would be much appreciated.

 

Thanks in advance,

Nick

 

 

Labels:
Message 1 of 9
1,180 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to Gooner_chef

‎08-12-2021 07:39 PM

Hi @Gooner_chef ,

Sorry for delay. I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to get the sum of amount which pass due date

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. Create a table visual apply the measure on it

yingyinr_0-1628822287199.png

Best Regards

View solution in original post

Adding a measure to summarize passed due payment amounts.pbix
Message 8 of 9
1,002 Views
8 REPLIES 8
Anonymous
Not applicable

‎08-04-2021 08:21 PM

Hi @Gooner_chef ，

On which two fields are the tables DIM_DATE and FACT_PAYMENT created relationships based on respectively? Could you please provide some sample data for these two tables (excluding sensitive data)? You can create a measure as below to get the sum of amount under the special conditions...

Measure =
CALCULATE (
    SUM ( FACT_PAYMENT[AMOUNT] ),
    FILTER (
        FACT_PAYMENT,
        FACT_PAYMENT[xxdate] > RELATED ( DIM_DATE[GREGORIANDATE] )
            && FACT_PAYMENT[CustRecId] = SELECTEDVALUE ( FACT_PAYMENT[CustRecId] )
    )
)

Best Regards

Message 2 of 9
1,088 Views
0
Gooner_chef
Regular Visitor
In response to Anonymous

‎08-04-2021 09:02 PM

Hi yingyinr,

Thank you so much for the reply.

This looks close. The relationship is [FACT_PAYMENT].[DueDateRecId] = [DIM_DATE].[DateRecId].

The actual date value is stored in [DIM_DATE].[GregorianDate]. So I would first need to join the two tables, and then do the date comparison.

Hopes this makes sense!

 

 

Message 3 of 9
1,082 Views
0
Anonymous
Not applicable
In response to Gooner_chef

‎08-05-2021 12:18 AM

Hi @Gooner_chef ，

According to the screenshot in your first post, you have created two relationships between table DIM_DATE and FACT_PAYMENT (one is active, the other one is inactive). This relationship ([FACT_PAYMENT]. [DueDateRecId] = [DIM_DATE]. [DateRecId]) is active or inactive? In addition to this relationship, which two columns in each of these two tables create the relationship based on? Could you please share some sample data from these two tables(exclude sensitive data) and the SQL statements where you used to achieve the same function in order to provide you with a suitable solution? Thank you.

yingyinr_0-1628147798911.png

Best Regards

Message 4 of 9
1,067 Views
Gooner_chef
Regular Visitor
In response to Anonymous

‎08-05-2021 07:50 AM

Hi @Anonymous 

Here is the SQL I used to achieve a similar result:

 

 

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

 

The tables name are slightly different, but the principle is the same.

 

Yes, there are mutliple relations to the DIM_DATE table (DueDateRecId and TransDateRecId). DueDateRecId will be the active relation.

 

Message 6 of 9
1,041 Views
0
Gooner_chef
Regular Visitor
In response to Gooner_chef

‎08-05-2021 07:53 AM

@Anonymous 

Here is some example data:

FACT_PAYMENT:

Gooner_chef_0-1628175191725.png

 

DIM_DATE:

Gooner_chef_1-1628175220867.png

 

Message 7 of 9
1,038 Views
0
Anonymous
Not applicable
In response to Gooner_chef

‎08-12-2021 07:39 PM

Hi @Gooner_chef ,

Sorry for delay. I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to get the sum of amount which pass due date

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. Create a table visual apply the measure on it

yingyinr_0-1628822287199.png

Best Regards

Adding a measure to summarize passed due payment amounts.pbix
Message 8 of 9
1,003 Views
Gooner_chef
Regular Visitor
In response to Anonymous

‎08-13-2021 08:01 AM

Wow! This is really cool. Using variables make this very easy to understand! Thank you so much for your help 🙂

Message 9 of 9
989 Views
0
Gooner_chef
Regular Visitor
In response to Anonymous

‎08-05-2021 07:17 AM

Hi @Anonymous 

Yes, you are correct. DIM_DATE is a role-playing dimension in this instance. The FACT_PAYMENT contains two relations to DIM_DATE; TransDateRecid and DueDateRecId. For simplicity, I can ensure that the DueDate relation is active.

 

Here is example SQL I used to achieve the result I need:

 

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

 

The tables/column have a slighlty different name, but the principle is the same.

 

Here is some example data.

 

DIM_DATE:

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

 

FACT_PAYMENT:

AmountCustRecIdDueDateRecIdTransDateRecId
3155637145326Aug  1 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
10005637146079Aug  1 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
19.355637146079Aug 31 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
4205637145326Sep  1 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
5255637145326Oct  1 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
40005637145326Nov  6 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
6005637146089Nov  9 2020 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
6005637145326Jan 11 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
-9.795637144576Mar 10 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
174.955637144576Mar 10 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
9.795637144576Mar 10 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
-9.795637144576Mar 11 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
9.795637144576Mar 11 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
19.585637145327Mar 13 2021 12:00AM-1-FiscalCalendarAug  1 2020 12:00AM-1-FiscalCalendar
-32.545637144576Mar 18 2021 12:00AM-1-FiscalCalendarAug  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

 

Hope this helps!

 

Nick

Message 5 of 9
1,020 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All