Forum Discussion

jdugas's avatar
jdugas
Advocate I
9 years ago
Solved

Latest Value

Hi   I'm trying to show only the latest value based on latest date and can't quite figure out what to do. I've tried filtering using LASTDATE but that doesn't appear to be working.   Here's a sam...
Need Help
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi jdugas,

     

    There are some solutions creating measure that will work with chart but not working in table, so my solution is focusing on group by data by Code and Max date column:

    • Create group by table with 2 column Code and Max YearMonth of that code
    Lastest = SUMMARIZE(Data,Data[Code],"YearMonth",MAX(Data[YearMonth]))
    • Join original table with above table to get the value
    Lastest Values = SUMMARIZE( FILTER(CROSSJOIN(Data,Lastest),Data[Code]=Lastest[Code] && Data[YearMonth]=Lastest[YearMonth]),Data[Code],Data[YearMonth],Data[FIValue])

     

     

    If this works for you please accept it as solution and also like to give KUDOS.

    Best regards
    Tri Nguyen

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi jdugas,

 

According to your description, you want get lastdate's value of each type, right?

 

If this is a case, you can refer to below formula to get the result table:

 

Table formula:

Result Table = ADDCOLUMNS(SUMMARIZE(Sheet5,Sheet5[Code],"MaxDate",MAX(sheet5[yearMonth])),"Value",LOOKUPVALUE(Sheet5[FIValue],Sheet5[Code],[Code],Sheet5[yearMonth],[MaxDate]))

 

 

In addition, you can also use measure to get the result value:

 

LastValue =
var curr= LASTNONBLANK(Sheet5[Code],[Code])
return
LOOKUPVALUE(Sheet5[FIValue],Sheet5[yearMonth],MAXX(FILTER(ALL(Sheet5),Sheet5[Code]=curr),[yearMonth]),Sheet5[Code],curr)

 

Visual:

 

Regards,

Xiaoxin sheng

Alex_Ooi's avatar
Alex_Ooi
Helper IV
6 years ago
Anonymous wrote:

Hi jdugas,

 

According to your description, you want get lastdate's value of each type, right?

 

If this is a case, you can refer to below formula to get the result table:

 

Table formula:

Result Table = ADDCOLUMNS(SUMMARIZE(Sheet5,Sheet5[Code],"MaxDate",MAX(sheet5[yearMonth])),"Value",LOOKUPVALUE(Sheet5[FIValue],Sheet5[Code],[Code],Sheet5[yearMonth],[MaxDate]))

 

 

In addition, you can also use measure to get the result value:

 

LastValue =
var curr= LASTNONBLANK(Sheet5[Code],[Code])
return
LOOKUPVALUE(Sheet5[FIValue],Sheet5[yearMonth],MAXX(FILTER(ALL(Sheet5),Sheet5[Code]=curr),[yearMonth]),Sheet5[Code],curr)

 

Visual:

 

Regards,

Xiaoxin sheng

I tried replicating this solution and I encountered an error where it says "A table of multiple values was supplied where a single value was expected."

I wonder what went wrong...