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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrMP
Helper III
Helper III

Result of measure in previous row

Hi,

 

I have table structure like this:

IdentifierRow IndexQuantity (Measure)Price (Measure)Previous Price
ABC12350100190<blank>
ABC12355120202190
ABC12385140196202
ABC12311280210196
ABC12352595204210

Is it possible to get Previus Price as a new measure from the measure? Note that every row has index so basicaly just finding the Price for the previous Row Index where Identifier is the same. I just couldnt make it work in vars.

 

Thank you!

1 ACCEPTED SOLUTION

I quite do not understand what you want.

I assume you want to change your sample like the below to consider various types of identifiers.

Please check the below picture.

 

Picture2.png

 

Previous Price =
VAR currentrowindex =
MAX ( 'Table'[Row Index] )
VAR currentidentifier =
MAX ( 'Table'[Identifier] )
VAR previousrowindex =
CALCULATE (
MAX ( 'Table'[Row Index] ),
FILTER (
ALL ( 'Table' ),
'Table'[Row Index] < currentrowindex
&& 'Table'[Identifier] = currentidentifier
)
)
RETURN
CALCULATE (
[Price total],
'Table'[Row Index] = previousrowindex
&& 'Table'[Identifier] = currentidentifier
)
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

Hi, @MrMP 

Please try the below.

 

Picture1.png

 

Previous Price =
VAR currentrowindex =
MAX ( 'Table'[Row Index] )
VAR previousrowindex =
CALCULATE (
MAX ( 'Table'[Row Index] ),
FILTER ( ALL ( 'Table' ), 'Table'[Row Index] < currentrowindex )
)
RETURN
CALCULATE ( [Price total], 'Table'[Row Index] = previousrowindex )

 

 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi,

 

previusrowindex also has to be constrained to identifier. how to add that?

Hi, @MrMP 

Thank you for your feedback, and sorry that I quite do not understand your question.

I assume you want to see the previous index number in a table as well.

 

Previous row index =
VAR currentrowindex =
MAX ( 'Table'[Row Index] )
VAR previousrowindex =
CALCULATE (
MAX ( 'Table'[Row Index] ),
FILTER ( ALL ( 'Table' ), 'Table'[Row Index] < currentrowindex )
)
RETURN
 previousrowindex

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


For Previusrowindex I have to add also filter for Identifier since there are many identifiers in the table.

I quite do not understand what you want.

I assume you want to change your sample like the below to consider various types of identifiers.

Please check the below picture.

 

Picture2.png

 

Previous Price =
VAR currentrowindex =
MAX ( 'Table'[Row Index] )
VAR currentidentifier =
MAX ( 'Table'[Identifier] )
VAR previousrowindex =
CALCULATE (
MAX ( 'Table'[Row Index] ),
FILTER (
ALL ( 'Table' ),
'Table'[Row Index] < currentrowindex
&& 'Table'[Identifier] = currentidentifier
)
)
RETURN
CALCULATE (
[Price total],
'Table'[Row Index] = previousrowindex
&& 'Table'[Identifier] = currentidentifier
)
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


sayaliredij
Super User
Super User

Hi,

 

You can try the following measures 

 

Previous =
VAR currentindex =
MAX ( 'Table'[Row Index] )
VAR Result =
CALCULATE (
[Price Measure],
'Table'[Row Index] < currentindex
)
RETURN
Result

 

Regards,

Sayali

 

If this post helps, then please consider Accept it as the solution to help others find it more quickly





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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