Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I would like to get the sum of the field "Value" for each year where the "Year" equals "ValueYear". But if "Year" is bigger than the biggest "ValueYear" then use "Value" from the rows with the biggest "ValueYear". So in this case below both 2021 and 2022 would show 506 as a result.
I tried this but it only triggers "FinnsEj"
SumValue =
VAR Finns = CALCULATE(SUMX('Meters', 'Meters'[Value]), FILTER('Meters', 'Meters'[ValueYear]= 'Meters'[Year]))
VAR FinnsEj = CALCULATE(SUMX('Meters', 'Meters'[Value]), FILTER('Meters', 'Meters'[ValueYear]= MAX('Meters'[ValueYear])))
RETURN
IF(SUMX('Meters', 'Meters'[Year]) > MAXX('Meters', 'Meters'[ValueYear]), FinnsEj, Finns)
Table: Meters
Meter | year | part | ValueYear | Value |
1337 | 2022 | H2 | 2021 | 253 |
1337 | 2022 | H1 | 2021 | 253 |
1337 | 2022 | H1 | 2019 | 238 |
1337 | 2022 | H2 | 2019 | 238 |
1337 | 2022 | H2 | 2017 | 222 |
1337 | 2022 | H1 | 2017 | 222 |
1337 | 2022 | H1 | 2020 | 239 |
1337 | 2022 | H2 | 2020 | 239 |
1337 | 2022 | H2 | 2018 | 230 |
1337 | 2022 | H1 | 2018 | 230 |
1337 | 2021 | H1 | 2021 | 506 |
1337 | 2021 | H2 | 2021 | 506 |
1337 | 2021 | H2 | 2019 | 475 |
1337 | 2021 | H1 | 2019 | 475 |
1337 | 2021 | H1 | 2017 | 444 |
1337 | 2021 | H2 | 2017 | 444 |
1337 | 2021 | H1 | 2020 | 477 |
1337 | 2021 | H2 | 2020 | 477 |
1337 | 2021 | H1 | 2018 | 459 |
1337 | 2021 | H2 | 2018 | 459 |
Thanks in advance!
Br
Johannes
Solved! Go to Solution.
Is this what you are looking for?
Thanks for this tidy solution 😃
@JohannesM , if this is a column, then try like
a new column
SumValue =
VAR Finns = CALCULATE(SUMX('Meters', 'Meters'[Value]), FILTER('Meters', 'Meters'[ValueYear]= earlier('Meters'[Year])))
VAR FinnsEj = CALCULATE(SUMX('Meters', 'Meters'[Value]), FILTER('Meters', 'Meters'[ValueYear]= MAX('Meters'[ValueYear])))
RETURN
IF('Meters'[Year] > 'Meters'[ValueYear], FinnsEj, Fin)
Hi and thanks,
I tried it as a column but got some kind of total for every row. Also, I would like to have it as a measure instead of a new column if possible. But at this rate I would take a new column solution if that fixes things 😃
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |