Creating New Table by Summarizing rows based on Latest Date

[Pmike]

I have the following question on creating a new table.

My table consists of the following example:

CURRENT TABLE

Customer     Date             Image

A                  10/1/2017    Image 1

A                  10/2/2017    Image 2

B                  9/1/2017      Image 6

B                  10/3/2017    Image 7

I need to create a new table that shows the following by grabbing all row data from the latest dates

NEW TABLE

Customer     Date             Image

A                  10/2/2017    Image 2

B                  10/3/2017    Image 7

I've tried many iterations of summarizing the table but can't seem to get it to work.

Any help would be greatly appreciated.

Thanks,

PMike.

[Pmike]

All, thank you for great solutions.  I am going to run these through today to test.

[Ashish_Mathur]

Hi,

Try these calculated field formulas

Last date = MAX(Data[Date])

Image on last date = LOOKUPVALUE(Data[Image],Data[Date],[Last date])

[parry2k]

Add a column using following expression to flag the most recent record and then filter on that

Recent = 
var r = RANKX(FILTER(Image, Image[Customer] = EARLIER(Image[Customer])), Image[Date], , DESC, DENSE)
return if(r = 1, "Yes", "")

[Zubair_Muhammad · Accepted solution]

@Pmike

If you want a calculated table. Try this.
Go to modelling tab...Press "New Table"

New Table =
ADDCOLUMNS (
    SUMMARIZE (
        CurrentTable,
        CurrentTable[Customer],
        "Max Date", MAX ( CurrentTable[Date] )
    ),
    "Image", CALCULATE (
        SELECTEDVALUE ( CurrentTable[Image] ),
        FILTER (
            CurrentTable,
            CurrentTable[Date] = [Max Date]
                && CurrentTable[Customer] = EARLIER ( CurrentTable[Customer] )
        )
    )
)

[Pmike]

Thanks Zubair, this was exactly what I was looking to accomplish.