Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago

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's avatar
Ashish_Mathur
Super User
5 years ago

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's avatar
    PowerBI123456
    Post Partisan
    5 years ago

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