Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

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.

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

Thanks in advance!

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 😃

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors