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.
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
Solved! Go to Solution.
Hi @leandroduarte ,
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
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
Hi @leandroduarte ,
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
Did I answer your question? Mark my post as a solution!
Hi @leandroduarte ,
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
Did I answer your question? Mark my post as a solution!
Hi @leandroduarte ,
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |