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.

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.