Payment Date After Note Date

[PowerBI123456]

Hi, I have 2 tables, notes and payments. I am trying to see which notes had a payment date after the note date Here is the sample model: Sample File 

Here is what I want it to look like. I am dealing 10million+ rows so if possible in an efficient measure. Thank you!!

[v-alq-msft · Accepted solution]

Hi, @PowerBI123456 

You may create a calculated column and measures as below. The pbix file is attached in the end.

Calculated column:

Flag Column = 
var postdate = 
CALCULATE(
    MAX('FACT: Payment'[Post Date]),
    FILTER(
        ALL('FACT: Payment'),
        [Account]=EARLIER('FACT: Notes'[Account])
    )
)
return
IF(
    postdate>[Note Date],
    1,0
)

Measure:

Flag Measure = 
var postdate = 
CALCULATE(
    MAX('FACT: Payment'[Post Date]),
    FILTER(
        ALL('FACT: Payment'),
        [Account]=MAX('FACT: Notes'[Account])
    )
)
return
IF(
    postdate>MAX([Note Date]),
    1,0
)

PostDate = 
CALCULATE(
    MAX('FACT: Payment'[Post Date]),
    FILTER(
        ALL('FACT: Payment'),
        [Account]=MAX('FACT: Notes'[Account])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello, sorry. How can I make that the 1 is glued only to the last date of management, and that if the account is managed on May 16, May 19 and May 26, and payment on May 17 and May 27 I stick the 1 only to the procedures of May 16 and 26 since they are those that would be taken as fulfilled since in the management of May there is no payment.

[pranit828]

Hi @PowerBI123456 

Create a new column 

check = IF(Payment[Post Date] > Notes[Note Date],1,0)

Now filter on the check column to show only 1

[PowerBI123456]

Hi - what table should I create that column in?

[PowerBI123456]

@pranit828  Thanks! Anyway to do in a measure instead of a column?