Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
84 | |
70 |