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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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