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