Forum Discussion

Pmike's avatar
Pmike
Advocate I
8 years ago
Solved

Creating New Table by Summarizing rows based on Latest Date

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.

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] )
        )
    )
)

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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's avatar
        Pmike
        Advocate I
        8 years ago

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

         

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try these calculated field formulas

     

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

  • Pmike's avatar
    Pmike
    Advocate I
    8 years ago

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