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