Get the most recent value as it is

[Mat87]

So I have a table like that looks like this:

What I want is to create a measure that get me the most recent value for each ID. So when I view the data in a pivottable it give me:

AA 12

BB 20

CC 15

DD 22

Any idea how I can get this done in DAX/powerpivot? 

[v-jiascu-msft]

@Mat87

Hi,

Could you tell me please if your problem is solved? You can mark the right answer to help others if it's convenient for you.

Best Regards!
Dale

[v-jiascu-msft]

@Mat87

Hi,

Following @Greg_Deckler's idea, you need to do these steps.

1. Choose columns 2007, 2008, 2009, 2010 with “Ctrl” pressed in Query Editor.
2. Click “Unpivot Columns”.
3. Change names “Attribute” to “Year”,.
4. Create a measure:

Measure =

CALCULATE (

    MIN ( Table1[Value] ),

    FILTER ( Table1, Table1[Attribute] = MAX ( Table1[Attribute] ) )

)

If you only have columns 2007, 2008, 2009, 2010, you still can try a calculated column.

LatestValue =

IF (

    ISBLANK ( [2010] ),

    IF (

        ISBLANK ( [2009] ),

        IF ( ISBLANK ( [2008] ), IF ( ISBLANK ( [2007] ), BLANK (), [2007] ), [2008] ),

        [2009]

    ),

    [2010]

)

Best Regards!

Dale

[Greg_Deckler]

You are going to want to unpivot your data on import so that you get your table in a format like:

ID, Year, Value

You'll do this in the Query Editor. At that point, your problem is fairly trivial.