Summarize a table Group by one column filtering another column a status and another by the max value

[Matt_HD]

Hi

I have a table as follows

Ident  Review Status          Date

123     Complete                01/12/2019

123     Complete                28/02/2020

123     Scheduled               08/02/2020

111     Complete                15/04/2020

134     Scheduled               30/04/2020 

I'd like to create a new table with only the latest complete reviews per Ident im assuming i need a combination of summarize, Group and filter but cant get it right. Can anyone help?

[Matt_HD]

Hi

just to advise none of these solutions worked 

my resolution was to bring in the same table again with some filters on the query to give me a redacted data set, I then created a helper column and could create the new table I needed with this.

Not particularly elegant but got the job done for now.

[jstorm]

Use the LASTDATE() function in a CALCULATE measure. 

Ex.

CALCULATE(

    SUM( 'Table'[Values] ),
    LASTDATE( 'Table'[Date] )
)

This should only return the only latest value for each row in your matrix or table visual.

[Matt_HD]

I’m not sure that’s what I’m looking for, I need a table really with distinct ident and date of latest completed

[camargos88]

Hi @Matt_HD ,

Try this code for a new table:

T =

ADDCOLUMNS(SUMMARIZE('Table'; 'Table'[Ident]; "Date"; MAX('Table'[Date])); "Review Status"; CALCULATE(DISTINCT('Table'[Review Status]); FILTER('Table'; 'Table'[Ident] = EARLIER('Table'[Ident]) && [Date] = EARLIER([Date]))))

Ricardo

[jstorm]

Try this:

SUMMARIZE(
    'Table',
    [Ident],

    "Date", IF(
        'Table'[ReviewStatus] = "Completed",
        MAX( 'Table'[Date] )
    )
)