Calculated table to return distinct values based on max dates.

[danextian]

Hi All,

I have a table with columns Sold-to Party (unique customer number) and Party Name (customer name which is not unique and more than one customer number may share the same Party Name). The latter changes over time while  the former remains the same.  My data source is a business warehouse (sort of a data  dump) that doesn't retroactively change the past data when, say, a field like Party Name gets updated.  What I'm trying to accomplish is return the latest Party Name based on the latest invoice date of Sold-to Party. I created this measure to get the max date:

maxinvoicedate =
CALCULATE (
    MAX ( 'WW Invoiced Orders'[Billing Date] ),
    ALLEXCEPT ( 'WW Invoiced Orders', 'WW Invoiced Orders'[Sold-to Party] )
)

And then this DAX formula to create a calculated table.

Unique Partners =
CALCULATETABLE (
    ALL ( 'WW Invoiced Orders'[Sold-to Party], 'WW Invoiced Orders'[Partner Name] ),
    FILTER (
        'WW Invoiced Orders',
        'WW Invoiced Orders'[Billing Date] = [maxinvoicedate]
    )
)

The formulas don't work like I want them to. The calculated table returns Sold-to Party and all Partner Names related to it instead of the latest one.  For example, if the max date is 6/28/17, the calculated table should return just the Partner Name for that date. Also it is highly unlikely to have more than one Party Name under the same Sold-to Party on the same date.

I 

[v-chuncz-msft · Accepted solution]

@danextian,

Use RANKX Function instead. Here is a similar case for your reference.