Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have 2 tables (notes & payments) that have 2 different dates (note date and payment date) and have a relationship between the account numbers in the 2 tables. How can I create a measure to see how many accounts have a payment after the note date? Thanks!
Solved! Go to Solution.
Hi @PowerBI123456 ,
If your notes date table has single notes date for each account number, you can create a measure like this to count:
Count =
VAR tab =
FILTER (
ALL ( Payment ),
'Payment'[Payment date]
> CALCULATE (
MIN ( 'Notes'[Note date] ),
'Notes'[Account number] IN DISTINCT ( 'Payment'[Account number] )
)
)
VAR tb =
SUMMARIZE (
ADDCOLUMNS (
tab,
"Count",
COUNTX (
FILTER ( tab, [Account number] = EARLIER ( Payment[Account number] ) ),
[Payment date]
)
),
[Account number],
[Count]
)
RETURN
COUNTX ( FILTER ( tb, [Count] > 1 ), [Account number] )
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PowerBI123456 ,
If your notes date table has single notes date for each account number, you can create a measure like this to count:
Count =
VAR tab =
FILTER (
ALL ( Payment ),
'Payment'[Payment date]
> CALCULATE (
MIN ( 'Notes'[Note date] ),
'Notes'[Account number] IN DISTINCT ( 'Payment'[Account number] )
)
)
VAR tb =
SUMMARIZE (
ADDCOLUMNS (
tab,
"Count",
COUNTX (
FILTER ( tab, [Account number] = EARLIER ( Payment[Account number] ) ),
[Payment date]
)
),
[Account number],
[Count]
)
RETURN
COUNTX ( FILTER ( tb, [Count] > 1 ), [Account number] )
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This assumes one note and payment date per account:
Measure =
COUNTROWS (
FILTER (
DISTINCT ( Notes[AccountID] ),
CALCULATE ( Payments[Date] ) > Notes[Date]
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@PowerBI123456 , Create account number as common dim. If reation is Many ot Many - https://www.seerinteractive.com/blog/join-many-many-power-bi/
Then create a measure like
countx(values(Account[Accountno]) , if(max(Payment[payment date])> max(note[note date]),Account[Accountno], blank()))
@amitchandak what if its already connected as a one to many relationship? The note table is the one sided and payment is many.
Hi,
In the Payments table, write this calculated column formula
Note date = related(notes[date])
To your card visual, drag these measure
Accounts = distinctcount(payments{account_id])
Accounts with payment date after note date = calculate([accounts],filter(payments,payments[data]>paments[note date]))
Hope this helps.
@Ashish_Mathur Thanks! But I am dealing with millions of rows so trying to avoid calculated columns if possible.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |