Create a Calculate index per year

[leandroduarte]

Dear All, I'm looking for a dax formula to create a calculate index for each year as per table below:

basically it would take the first item (A) and divide by the value *100 per year.

Year	Item	Values	Index Year Calculated example expected
2019	A	2450	100
2019	B	3000	122
2019	C	1900	78
2020	A	6000	100
2020	B	5500	92
2020	C	9000	150

[v-xicai · Accepted solution]

Hi @leandroduarte ,

You may create measure like DAX below.

Index Year =
VAR ValueA =
    CALCULATE (
        MAX ( Table1[Values] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Year] = MAX ( Table1[Year] )
                && Table1[Item] = "A"
        )
    )
VAR Current =
    MAX ( Table1[Values] )
RETURN
    DIVIDE ( ValueA * 100, Current )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@leandroduarte , expected output is not clear

is it

divide([Value],[index Year])*100

or

divide(Sumx(filter(Table, [Year] =earlier([Year]) && [Item] ="A"),[Values]),umx(filter(Table, [Year] =earlier([Year]) && [Item] ="A"),[ Index Year]))*100

[leandroduarte]

it basically need always to compare to the Values depending on year and fixing on the Iteam A