Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago
Solved

Payment Date After Note Date

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!!

 

 

Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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