Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
Hi,
Can you please help me filter value of the last year for each company in the table below?
The result in the last column should be 4 for company A, 200 for company C, and 4 for company B.
I tried to use lookupvalue, filter, and max but I couldn't figureout the arrangement in the formula.
Year | Value | Company | Value in the final year |
2019 | 10 | A | |
2020 | 4 | A | |
2021 | 5 | A | |
2022 | 4 | A | |
2019 | 8 | C | |
2020 | 9 | C | |
2021 | 7 | C | |
2022 | 200 | C | |
2019 | 10 | B | |
2020 | 4 | B | |
2021 | 5 | B | |
2022 | 4 | B |
Solved! Go to Solution.
Hi @Anonymous ,
Measure way:
Measure =
VAR maxyear =
CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) )
RETURN
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Company] ), 'Table'[Year] = maxyear )
)
Alternatively,to use a calculated column as below.
in the final year =
VAR maxyear =
CALCULATE (
MAX ( 'Table'[Year] ),
FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) )
)
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Year] = maxyear
&& 'Table'[Company] = EARLIER ( 'Table'[Company] )
)
)
Pbix as attached.
Hi @Anonymous ,
Measure way:
Measure =
VAR maxyear =
CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) )
RETURN
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Company] ), 'Table'[Year] = maxyear )
)
Alternatively,to use a calculated column as below.
in the final year =
VAR maxyear =
CALCULATE (
MAX ( 'Table'[Year] ),
FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) )
)
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Year] = maxyear
&& 'Table'[Company] = EARLIER ( 'Table'[Company] )
)
)
Pbix as attached.
Hi @Anonymous
try a measure
Value in the final year=
var _lastYear = calculate(max('Table'[Year]),ALLEXCEPT('Table','Table'[Company]))
RETURN
LOOKUPVALUE('Table'[Value], 'Table'[Company],SELECTEDVALUE('Table'[Company]), 'Table'[Year],_lastYear)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Sorry @Anonymous
it must be comma
Value in the final year=
var _lastYear = calculate(max('Table'[Year]),ALLEXCEPT('Table','Table'[Company]))
RETURN
LOOKUPVALUE('Table'[Value], 'Table'[Company],SELECTEDVALUE('Table'[Company]), 'Table'[Year],_lastYear)
do not hesitate to give a kudo to useful posts and mark solutions as solution
@Anonymous
where do yo try to see the result?
table visual based on your data sample
do not hesitate to give a kudo to useful posts and mark solutions as solution
I added as a column for my current table
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |