cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Can´t get the IF-statement to do what i want

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

Br
Johannes

1 ACCEPTED SOLUTION
Super User

Is this what you are looking for?

4 REPLIES 4
Super User

Is this what you are looking for?

Helper I

Thanks for this tidy solution 😃

Super User

@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)

Helper I

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 😃

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors