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

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
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

  • Oscar_Mtz_V's avatar
    Oscar_Mtz_V
    Kudo Commander
    8 years ago

    Thanks!!! Great Answer!!!

  • jdugas's avatar
    jdugas
    Advocate I
    9 years ago

    Looks like I got it to work....or you did! Thanks

    • jdugas's avatar
      jdugas
      Advocate I
      9 years ago

      How would I filter these tables? I have a table with many values and each record has an ID (DsfID). I can't seem to get this to work until I filter the DsfID at the query level. When I do that, my report breaks other visuals using the same table but with a different DsfID.

       

      DsfID is a field in a table that tracks multiple datasets which don't necessarily relate to each other. It's a table that enables us to capture data on our members such as satisfaction scores, target patient census numbers, contributions to profitability, and more. My above challenge is something I will need to do for multiple DsfIDs so I'm looking for a way to filter at the SUMMARIZE level and not at the report level as a whole.

       

      Jeremy

      • tringuyenminh92's avatar
        tringuyenminh92
        Memorable Member
        9 years ago

        Hi jdugas,

         

         

        I'm not sure that i understand your point or not. Does this description relate the main topic or this is another question? 

        Could you show me the some fields in data table and your expectation as picture?