Getting latest date from date column where there are multiples of a value in another column

[webstark]

Hi,

I have a table with two columns; a SiteID column (number) where there could be mutiple rows for each site ID and a DateAdded field which would have the date the equipment was added to the site.

I want to create a another column which compares the dates for mulitple entries of the same SiteID and returns the latest date of how ever many there are

SiteID       Date Added                  Last Date

--------|-------------------------|--------------

1         |    1/1/2016 00:00:00

1         |    15/5/2016 00:00:00

1         |      2/11/2016 00:00:00

1         |                                     |  2/11/2016 00:00:00

Can this be done?

[Sean · Accepted solution]

@webstark

It looks like you are asking for a COLUMN formula

Last Date Added COLUMN =
CALCULATE (
    LASTDATE ( TableName[Date Added] ),
    ALLEXCEPT ( TableName, TableName[SiteID] )
)

If you want a MEASURE instead then try this

Last Date Added MEASURE =
CALCULATE ( LASTDATE ( TableName[Date Added] ), VALUES ( TableName[SiteID] ) )

Hope this helps!