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 see how many accounts have a payment after the note date? Thanks!

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.

7 Replies

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

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

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

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

  • v-yingjl's avatar
    v-yingjl
    Community Support
    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.