Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
fmaestri
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:

DateValueLast value
MarchA 
AprilBA
MayCB
JuneDC

 

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:

DateValueindex
MarchA100
AprilB103,1
MayC108,3
JuneD113,5
.........

 

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

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@fmaestri 

Daniel29195_0-1706196831893.png

 

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

 

View solution in original post

3 REPLIES 3
Daniel29195
Super User
Super User

@fmaestri 

Daniel29195_0-1706196831893.png

 

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

 

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

@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

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.