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

## Index for minimum year and per Item

Dear Colleagues,

I'm trying to find a Dax formula build an index to compare teh minimum year and item with the next year figures.

Please see below the example and expected results = column Index

 year Item Value Index = Expected results 2011 A 120 100 For item A = The min should be based on 2011 figures 2012 A 130 108 Compare vs. 2011 for item A 2013 A 110 92 Compare vs. 2011 for item A 2014 A 100 83 Compare vs. 2011 for item A 2015 A 90 75 Compare vs. 2011 for item A 2016 A 120 100 Compare vs. 2011 for item A 2011 B 120 100 For item B = The min should be based on 2011 figures 2012 B 130 108 Compare vs. 2011 for item B 2013 B 110 92 Compare vs. 2011 for item B 2014 B 100 83 Compare vs. 2011 for item B 2015 B 90 75 Compare vs. 2011 for item B 2016 B 120 100 Compare vs. 2011 for item B

I appreciate if you can share some ideas

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Few questions to clearly understand what you want to achieve:

- You want to compare the value for an item with the earliest value of the same item (e.g. compare value of item A in 2015 with value of same item A in 2011, A in 2014 vs A in 2011, etc.). Correct ? If not, could you please give more information.

- Do you want to do this in a measure or in a calculated column?

If you aim to do it in a calculated column, you could write the following DAX:

``````VAR CurrentItem = table[Item]
VAR MinYear =
MINX(
FILTER( table, table[item] = CurrentItem )
table[year]
)

VAR ValueForMinYear =
MAXX(
FILTER(
table,
table[item] = CurrentItem &&
table[year] = MinYear
),
table[Value]
)

RETURN ValueForMinYear

or

RETURN table[Value] - ValueForMinYear``````

Hope it helps. Does it?

Best,

Thomas

Helper I

Dear Tomas

Yes, it is correct, I want to compare the value for an item with the earliest value of the same item (e.g. compare value of item A in 2015 with value of same item A in 2011, A in 2014 vs A in 2011, etc.).

In this case I would like to have as measure

I tried the expression you informed but it didnt work

Community Support

Could you pls advise how to get the expected result as you provided?

To my understanding,the result should be as below:

 year Item Value Index should be Index = Expected results 2011 A 120 0 100 2012 A 130 10 108 2013 A 110 -10 92 2014 A 100 -20 83 2015 A 90 -30 75 2016 A 120 0 100 2011 B 120 120 100 2012 B 130 10 108 2013 B 110 -10 92 2014 B 100 -20 83 2015 B 90 -30 75 2016 B 120 0 100

Best Regards,
Kelly

3 REPLIES 3
Community Support

Could you pls advise how to get the expected result as you provided?

To my understanding,the result should be as below:

 year Item Value Index should be Index = Expected results 2011 A 120 0 100 2012 A 130 10 108 2013 A 110 -10 92 2014 A 100 -20 83 2015 A 90 -30 75 2016 A 120 0 100 2011 B 120 120 100 2012 B 130 10 108 2013 B 110 -10 92 2014 B 100 -20 83 2015 B 90 -30 75 2016 B 120 0 100

Best Regards,
Kelly

Anonymous
Not applicable

Few questions to clearly understand what you want to achieve:

- You want to compare the value for an item with the earliest value of the same item (e.g. compare value of item A in 2015 with value of same item A in 2011, A in 2014 vs A in 2011, etc.). Correct ? If not, could you please give more information.

- Do you want to do this in a measure or in a calculated column?

If you aim to do it in a calculated column, you could write the following DAX:

``````VAR CurrentItem = table[Item]
VAR MinYear =
MINX(
FILTER( table, table[item] = CurrentItem )
table[year]
)

VAR ValueForMinYear =
MAXX(
FILTER(
table,
table[item] = CurrentItem &&
table[year] = MinYear
),
table[Value]
)

RETURN ValueForMinYear

or

RETURN table[Value] - ValueForMinYear``````

Hope it helps. Does it?

Best,

Thomas

Helper I

Dear Tomas

Yes, it is correct, I want to compare the value for an item with the earliest value of the same item (e.g. compare value of item A in 2015 with value of same item A in 2011, A in 2014 vs A in 2011, etc.).

In this case I would like to have as measure

I tried the expression you informed but it didnt work

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors