How to divide current value by first value in column (Fixed value) per year

[Anonymous]

Hello Community,

I am facing a little DAX problem, I have a what if Calculated measure

Price: Sum(price) + Selected (Price Parameter)

The result from this, I need to create a measure that basically takes my current value divided by my first value.

Index	Price (Measure Above)	Year
1	1.870	2008
2	1.840	2008
3	1.865	2008
4	1.880	2008

This a sample of data I have several year and for each year the index restart at 1

Index values (1-250) for each year 

I need for each year to calculate the current price value in table (index)  / price for 1 index day as below

Price (Index =2) /  Price (Index =1), Price (Index =3) /  Price (Index =1) , Price (Index = 4)/  Price (Index =1) and so on

How can I achieve this in dax?

Thanks so much!

[FrankAT · Accepted solution]

Hi @Anonymous 

you can do it like this:

Price calculation = 
VAR _PriceIndex_1 = 
    CALCULATE(
        MIN('Table'[Price]),
        FILTER(
            ALLEXCEPT('Table','Table'[Year]),
            'Table'[Index] = 1
        )
    )
VAR _ActualPrice = MIN('Table'[Price])
RETURN
    DIVIDE(_ActualPrice,_PriceIndex_1)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your description, you can do some steps as follows.

1. Create a calculated index column.( I created an index column in advance to mark the existing order.)

_index =

RANKX (

    FILTER (

        CASE,

        EARLIER ( CASE[Year] ) = CASE1[Year]

    ),

    'CASE'[Index],

    ,

    ASC

)

1. Create a ‘Price Calculation’measure.

Measure = DIVIDE(

MAX('CASE'[Price (Measure Above)]),

CALCULATE(

MAX('CASE'[Price (Measure Above)]),

FILTER(

ALLEXCEPT('CASE',CASE1[Year]),

[_index]=1

)

)

)

Result:

Best Regards,

Yuna

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

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your description, you can do some steps as follows.

1. Create a calculated index column.( I created an index column in advance to mark the existing order.)

_index =

RANKX (

    FILTER (

        CASE,

        EARLIER ( CASE[Year] ) = CASE1[Year]

    ),

    'CASE'[Index],

    ,

    ASC

)

1. Create a ‘Price Calculation’measure.

Measure = DIVIDE(

MAX('CASE'[Price (Measure Above)]),

CALCULATE(

MAX('CASE'[Price (Measure Above)]),

FILTER(

ALLEXCEPT('CASE',CASE1[Year]),

[_index]=1

)

)

)

Result:

Best Regards,

Yuna

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

[Anonymous · Accepted solution]

Hello All,

thank you very much for your help,

Fixing my DAX like this finally worked for me

Price Calculation =

VAR _PriceIndex_1 =

CALCULATE(MINX(Contracts_Zema,[Price + Price Parameter]),

FILTER(ALLEXCEPT('Contracts_Zema','Contracts_Zema'[Contract_Year],Contracts_Zema[Location],Contracts_Zema[Contract_Type]),

'Contracts_Zema'[Trade_Day] = 1

)

)

VAR _ActualPrice = MAXX(Contracts_Zema,[Price + Price Parameter])

RETURN

DIVIDE(_ActualPrice,_PriceIndex_1) -1

[FrankAT · Accepted solution]

Hi @Anonymous 

you can do it like this:

Price calculation = 
VAR _PriceIndex_1 = 
    CALCULATE(
        MIN('Table'[Price]),
        FILTER(
            ALLEXCEPT('Table','Table'[Year]),
            'Table'[Index] = 1
        )
    )
VAR _ActualPrice = MIN('Table'[Price])
RETURN
    DIVIDE(_ActualPrice,_PriceIndex_1)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Hi FrankAT 

Im not getting the same results. Could it be because my dataset has more fields than year ?

Price Calculation =

VAR _PriceIndex_1 =

CALCULATE(

MIN('Contracts_Zema'[Price]),

FILTER(ALLEXCEPT('Contracts_Zema','Contracts_Zema'[Contract_Year]),

'Contracts_Zema'[Trade_Day] = 1

)

)

VAR _ActualPrice = MIN('Contracts_Zema'[Price])

RETURN

DIVIDE(_ActualPrice,_PriceIndex_1)

[Anonymous]

Hey,

thank you so much

For me the results are not working, could it be because I have more dimensions in my data than year? so the all except doesnt work?

I also have location, Season?