get value of last row to create index numbers

[fmaestri]

Greetings,

I've seen this question many times, and I even used chat gpt to get it, but it just don't work. A recurrent thing I need to do is create index numbers, that is taking the first value as 100 (or 1 or 100%) and the next values is the porcentual variation. I always do this in excel extremly easy, buy after 2 years of power bi I just don't get it.

How do I actually get the value of the last row? Earlier never worked for me.

I want this:

Date	Value	Last value
March	A
April	B	A
May	C	B
June	D	C

And i've tried many, many things, like this one, and I just get a blank row:

last_value = 
IF(
    NOT(Table[date]=MIN(Table[date])),
    CALCULATE(
        MAX('Table'[value]),
        FILTER(
            ALL('Agregados'),
            'Table'[date] = Table[date] - 1
        )
    )
)

My goal is to have variations like this, but I would love to get as a variable the last row value. The index values I need to create look like this:

Date	Value	index
March	A	100
April	B	103,1
May	C	108,3
June	D	113,5
...	...	...

Any ideas in a extremly easy way? Thanks a lot!

[Daniel29195 · Accepted solution]

@fmaestri 

use this code in a calculated column : 

Column =

SELECTCOLUMNS(

OFFSET(-1,

    all(Table24[Value],Table24[index]),

    ORDERBY(Table24[index], asc)

) ,Table24[Value]

)

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button👍🤠

[fmaestri]

Thank you! That was very fast and works perfectly. ❤️

[Daniel29195]

@fmaestri 

i forgot to mention, that you can orderby any column you want , 

i created index for the date column so that i can sort asc by date 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button