Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
8 years ago
Solved

How to pick the correct payment date?

Hi All,

 

 

 In the picture, you can see that there is two payment date and I have to take the second payment date and there might multiple cases where payment comes in slots and I have to pick the last date of a particular invoiced against the payment received.

 

So please, Help me out of this!!!!

 

Regards,

Satish Kumar

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Satish_Kumar,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

    Column = 
CALCULATE (
    MAX ( 'Table1'[Paymnet Date] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[Invoice Date] = EARLIER ( Table1[Invoice Date] )
            && 'Table1'[Document No_] = EARLIER ( Table1[Document No_] )
    )
)

     

    Regards

6 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Satish_Kumar,

     

    If I understand you correctly, you should be able to use the formula below to create a measure to get the last date of a particular invoiced against the payment received. :smileyhappy:

    measure =
VAR invoiceAmount =
    MAX ( 'Table1'[Invoice Amount LCY] )
VAR documentNo =
    MAX ( 'Table1'[Document No_] )
RETURN
    CALCULATE (
        MAX ( 'Table1'[Payment Date] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Invoice Amount LCY] = invoiceAmount
                && 'Table1'[Document No_] = documentNo
        )
    )

    Remark: just replace 'Table1' with your real table name.

     

    Regards

    • Satish_Kumar's avatar
      Satish_Kumar
      Icon for Helper V rankHelper V
      8 years ago

      v-ljerr-msft

       

      The solution you provided I tried But it is not displaying anything.

       

      The Measure which was created with the formula you gave to me is showing blank value.

       

       

      Regards,

      Satish Kumar

       

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Satish_Kumar,

         

        Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that we can further assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

         

        Regards

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    If you want the answer to be in a calculated column of the Data Model i.e. the PowerPivot, then try this

     

    =CALCULATE(MAX(Data[Payment Date]),FILTER(Data,Data[Document No_]=EARLIER(Data[Document No_]))

     

    Hope this helps.