Forum Discussion
How to divide current value by first value in column (Fixed value) per year
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)Hi Anonymous ,
Based on your description, you can do some steps as follows.
- 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
)
- 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.
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])RETURNDIVIDE(_ActualPrice,_PriceIndex_1) -1
Hi Anonymous ,
Based on your description, you can do some steps as follows.
- 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
)
- 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.
Hello All,
thank you very much for your help,
Fixing my DAX like this finally worked for me