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

The 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.

Reply
PowerBI123456
Post Partisan
Post Partisan

Comparing Dates from 2 tables

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!

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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] )

notes.pngpayments.pngcount.png

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.

View solution in original post

7 REPLIES 7
v-yingjl
Community Support
Community Support

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] )

notes.pngpayments.pngcount.png

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.

AlB
Community Champion
Community Champion

Hi @PowerBI123456 

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 

 

SU18_powerbi_badge

 

@AlB  Hi, thanks but not working. Its asking for an expression after calculate. 

amitchandak
Super User
Super User

@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()))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur Thanks! But I am dealing with millions of rows so trying to avoid calculated columns if possible. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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