Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |