Index for minimum year and per Item

[leandroduarte]

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

[Anonymous · Accepted 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

[leandroduarte · Accepted solution]

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

[v-kelly-msft · Accepted 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!

[Anonymous · Accepted 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

[leandroduarte · Accepted solution]

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