Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a fairly intensive Power Query\ query, i.e. lots of transformation..
I want to reference a value within that same query, e.g. a prior quarter value.
I am currently duplicating the query and then merging-- but this is hurting the refresh performance.
Is there a way to "lookup", or reference, within the same query w/o duplicating\ merging?
Solved! Go to Solution.
Hi @kent-culpepper ,
You can create a calculated column or measure as below to get it, please find the details in the attachment.
Create a calculated column:
FMV Prior =
CALCULATE (
MAX ( 'Table'[FMV] ),
FILTER (
'Table',
'Table'[Investment] = EARLIER ( 'Table'[Investment] )
&& 'Table'[Quarter]
= EARLIER ( 'Table'[Quarter] ) - 1
)
)
Create a measure:
Measure =
VAR _investment =
SELECTEDVALUE ( 'Table'[Investment] )
VAR _qtr =
SELECTEDVALUE ( 'Table'[Quarter] )
RETURN
CALCULATE (
MAX ( 'Table'[FMV] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Investment] = _investment
&& 'Table'[Quarter] = _qtr - 1
)
)
In addition, you can refer the following links to achieve it in Power Query Editor.
Value from previous row – Power Query, M language – Trainings, consultancy, tutorials
Fast and easy way to reference previous or next rows in Power Query or Power BI
Best Regards
Hi @kent-culpepper ,
You can create a calculated column or measure as below to get it, please find the details in the attachment.
Create a calculated column:
FMV Prior =
CALCULATE (
MAX ( 'Table'[FMV] ),
FILTER (
'Table',
'Table'[Investment] = EARLIER ( 'Table'[Investment] )
&& 'Table'[Quarter]
= EARLIER ( 'Table'[Quarter] ) - 1
)
)
Create a measure:
Measure =
VAR _investment =
SELECTEDVALUE ( 'Table'[Investment] )
VAR _qtr =
SELECTEDVALUE ( 'Table'[Quarter] )
RETURN
CALCULATE (
MAX ( 'Table'[FMV] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Investment] = _investment
&& 'Table'[Quarter] = _qtr - 1
)
)
In addition, you can refer the following links to achieve it in Power Query Editor.
Value from previous row – Power Query, M language – Trainings, consultancy, tutorials
Fast and easy way to reference previous or next rows in Power Query or Power BI
Best Regards
Yes, possibly, but you'll need to provide some existing code and explain more specifically what you're trying to do.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
61 | |
61 | |
55 | |
38 | |
27 |
User | Count |
---|---|
85 | |
60 | |
45 | |
41 | |
39 |