Forum Discussion

webstark's avatar
webstark
Frequent Visitor
9 years ago
Solved

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

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's avatar
    Sean
    9 years ago

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

     

1 Reply

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

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