cancel
Showing results for
Did you mean:  Post Partisan

Comparing 2 dates

Hi - I have 2 tables, notes and payments on an account. I am trying to determine which accounts had a payment after the max note date. Here is the sample file: Sample File

Here is the notes table Here is the payments table: In this example, accounts 1 and 2 had a payment after the max note date and not account 3.

Here is my model. This is simplied, but I am dealing with millions of rows so ideally if this can be done in a measure that would be great. Appreciate the help! 1 ACCEPTED SOLUTION  Super User

Here is a measure that shows one way to do it.

Accts Late Payments =
VAR vSummary =
VALUES ( 'DIM: Account Numbers' ),
"cMaxNote",
CALCULATE (
MAX ( 'FACT: Notes'[Note Date] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
VAR vThisMax = [cMaxNote]
RETURN
CALCULATE (
COUNTROWS ( 'FACT: Payment' ),
'FACT: Payment'[Payment Date] > vThisMax
) >= 1
)
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

2 REPLIES 2  Super User

Here is a measure that shows one way to do it.

Accts Late Payments =
VAR vSummary =
VALUES ( 'DIM: Account Numbers' ),
"cMaxNote",
CALCULATE (
MAX ( 'FACT: Notes'[Note Date] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
VAR vThisMax = [cMaxNote]
RETURN
CALCULATE (
COUNTROWS ( 'FACT: Payment' ),
'FACT: Payment'[Payment Date] > vThisMax
) >= 1
)
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!  Post Partisan

@mahoneypat Thank you!!  