cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## get value of last row to create index numbers

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(
'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!

1 ACCEPTED SOLUTION
Super User

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👍🤠

3 REPLIES 3
Super User

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👍🤠

Frequent Visitor

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

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors