cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors