Forum Discussion

Casteless's avatar
Casteless
Icon for Helper I rankHelper I
9 years ago
Solved

Trying to calculate most recent classification

Suppose there are three columns:

 

CLIENT | Classification | Date

1          |   A                  | 1/1/2016

1          |   A                  | 1/1/2016

1          |   B                  | 2/1/2016

1          |  C                  | 3/1/2016

1          |  A                  | 4/1/2016

2          |   B                | 1/1/2016

2          |   B                  | 2/1/2016

2          |  A                  | 3/1/2016

2          |   A                 | 3/1/2016

2          |  C                  | 4/1/2016

 

 

How would I grab the most recent Classification for each client to output:

 

Client | Classification

1        | A

2        | C

 

I've tried multiple things but everything always returns all combinations e.g.

1        | A

1        | B

1        | C

2        | A

2        | B

2        | C

 

 

Any help?

  • Sean's avatar
    Sean
    9 years ago

    Casteless

     

    How about this?

     

    Classification on Last Date =
    IF (
        HASONEVALUE ( 'Table'[Client] ),
        CALCULATE (
            LASTNONBLANK ( 'Table'[Classification], 1 ),
            LASTDATE ( 'Table'[Date] )
        ),
        BLANK ()
    )

    Good Luck! :smileyhappy:

14 Replies

    • Casteless's avatar
      Casteless
      Icon for Helper I rankHelper I

      Hi Vvelarde, 

       

      That won't work as the rest of the values in the table require aggregation across a range of dates.

      As such the |Date| column can't be  a part of the visual

       

      Thanks for the attempt!

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion

        Casteless

         

        try with this:

         

        LastClassification = CALCULATE(VALUES(Table2[Classification]),LASTDATE(Table2[Date]))