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

willianmsantos's avatar
willianmsantos
Frequent Visitor
9 years ago

Hello Xiaoxin sheng and community!

 

 

Fisrt of all thanks for your posts and contribution, I wanted to reach the same results with a more extensive table, I followed the same Parameters from your solution:

 

*** Your code

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

 

*** My code adapted to my instance:

 

SG_LastValue =
var curr= LASTNONBLANK(SG_View_Jan_Abr[INSTANCE];[INSTANCE])
return
LOOKUPVALUE(SG_View_Jan_Abr[Allocated Capacity (GB)];SG_View_Jan_Abr[DATE].[Date];MAXX(FILTER(ALL(SG_View_Jan_Abr);SG_View_Jan_Abr[INSTANCE]=curr);[DATE]);SG_View_Jan_Abr[INSTANCE];curr)

 

 

I was expecting to get the last value based on the most recent date of each instance:

 

But when I tested that, it brings me blank values:

 

 

 

Can you help me and tell me if more parameters are needed or I missing anything? Basically I want to get from the table bellow a relation of INSTANCES and their Last Allocated capacity based on the most recent date. Bellow is a link of the table

 

https://drive.google.com/open?id=0Bx1SM-BuvfI4d1hGT0Rja3JncWM