Comparing Dates from 2 tables

[PowerBI123456]

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!

[v-yingjl · Accepted 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.

[AlB]

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 

[PowerBI123456]

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

[amitchandak]

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

[PowerBI123456]

@amitchandak what if its already connected as a one to many relationship? The note table is the one sided and payment is many. 

[Ashish_Mathur]

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.

[PowerBI123456]

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