Displaying the latest date for a subset of records in a table

[sirgseymour]

Hello,

I have a table that contains a list of Portfolios that are updated independantly periodically independantly and I want to display the latest date that each one of these portfolios were updates. In the table below, I want to calculate the LatestRollupdate for each Portfolio and then add an indicator for Latest/Not? I have used the following measures and column formulas but am unable to filter the measure by Portfolio. Any ideas?

Measure:

Last Date = CALCULATE(Table1[Rollup Date],ALL(Table1)) - Returns the laset date for whole table not filtered by Portfolio

Column:

Latest Portfolio = if(Table1[Rollup Date]=Table1[Last Date],"Latest","Not")

Portfolio	Rollup Date	Latest Rollup Date	Latest/Not?
1	01-09-18	10-09-18	Not
1	02-09-18	10-09-18	Not
1	04-09-18	10-09-18	Not
1	10-09-18	10-09-18	Latest
2	03-09-18	05-09-18	Not
2	05-09-18	05-09-18	Latest
3	05-10-18	12-11-18	Not
3	11-11-18	12-11-18	Not
3	12-11-18	12-11-18	Latest
4	01-10-18	22-10-18	Not
4	02-10-18	22-10-18	Not
4	03-10-18	22-10-18	Not
4	22-10-18	22-10-18	Latest

[Ashish_Mathur]

Hi,

Write these 2 calculated column formulas

Latest Rollup Date=CALCULATE(MAX(Table1[Rollup Date]),FILTER(Table1,Table1[Portfolio]=EARLIER(Table1[Portfolio])))

Latest/Not?=IF([Latest Rollup Date]=[Rollup Date],"Latest","Not")

Hope this helps.

[v-shex-msft · Accepted solution]

Hi @sirgseymour,

You can use below formula to achieve your requirement:

Measure formula:

Last Date = 
CALCULATE (
    MAX ( 'Sample'[Rollup Date] ),
    ALLSELECTED ( 'Sample' ),
    VALUES ( 'Sample'[Portfolio] )
)

Calculate column formula:

Is Last = 
VAR _lastdate =
    CALCULATE (
        MAX ( 'Sample'[Rollup Date] ),
        FILTER ( ALL ( 'Sample' ), [Portfolio] = EARLIER ( 'Sample'[Portfolio] ) )
    )
RETURN
    IF ( [Rollup Date] = _lastdate, "LastDate", "Not" )

Regards,
Xiaoxin Sheng

[sirgseymour]

Thank you that worked!!1