How to pick the correct payment date?

[Satish_Kumar]

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

[Ashish_Mathur]

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.

[Satish_Kumar]

Hi @Ashish_Mathur @v-ljerr-msft

https://drive.google.com/file/d/0Bxq6NdcHyxydVDZYcHh1WjJ3QU0/view?ts=59df17ae

This is the link where I put an excel sheet.

And I highlighted the payment date which should come against the Invoice Date.

Regards,

Satish 

[v-ljerr-msft · Accepted solution]

Hi @Satish_Kumar,

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

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

Regards

[v-ljerr-msft]

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. 

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]

@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]

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