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

     

     

     

  • willianmsantos's avatar
    willianmsantos
    Frequent Visitor
    9 years ago

    Xiaoxin sheng shen!

     

     

    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

     

     

     

  • kkalyanthaya's avatar
    kkalyanthaya
    Microsoft Employee
    8 years ago

    Thank you Anonymous for the answer. This works fine for me with imported data.

     

    But for push data set data, the measure gets created but no data is shown. The grid appears blank. Is this solution not applicable for push data set ?

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