Adjust price for each year by a index

[Anonymous]

Hello everyone, 

I want to show the regulation development for "MONEY" for each year by "INDEX". But I have problem creating a measure that can be shown in a tabel and graph visualisation.

I only have the average "MONEY" for year 2015, and i want to show how the average "MONEY" would be in 2016 by the changes in "INDEX" and again for 2017 and so on.

The adjusted amount in 2016 can be calculated as 100.5 (the index in 2016) divided by 100 (the index in 2015) times the "MONEY" 6130 -> 6130 * 100.5 / 100 = 6160,65.

I have a main table:

And a Index table:

The visualisation output i want in Power BI should be looking like this:

BI example link: https://drive.google.com/drive/folders/1dnpW0s8UaT5KrzEaHtOOuL72ti8xdbDG?usp=sharing

Thank you very much.

[Greg_Deckler]

@Anonymous Maybe:

Measure =
  VAR __Sum = SUM('Table1'[MONEY])
  VAR __EarliestYear = MAXX(ALL('Table2'),[YEAR])
  VAR __EarliestIndex = MAXX(FILTER('Table2',[YEAR]=__EarliestYear),[INDEX])
  VAR __CurrentYear = MAX('Table2'[YEAR])
  VAR __CurrentIndex = MAXX(FILTER('Table2',[YEAR]=__CurrentYear ),[INDEX])
RETURN
  __Sum * DIVIDE(__CurrentIndex, __EarliestIndex)

[Anonymous]

Hi @Greg_Deckler thanks for the reply. 

When I'm creating the measure in my sample I get: 

The result only gives me for 2021 (And its actually the 2015 result). I also need 2015, 2016, 2017, 2018, 2019, 2020 prices. 

[Greg_Deckler · Accepted solution]

@Anonymous Minor tweaks:

Measure = 
  VAR __Average = AVERAGEX(ALL('DATASET'),[MONEY])
  VAR __EarliestYear = MINX(ALL('INDEX'),[YEAR])
  VAR __EarliestIndex = MAXX(FILTER(ALL('INDEX'),[YEAR]=__EarliestYear),[INDEX])
  VAR __CurrentYear = MAX('INDEX'[YEAR])
  VAR __CurrentIndex = MAXX(FILTER('INDEX',[YEAR]=__CurrentYear ),[INDEX])
RETURN
  __Average * DIVIDE(__CurrentIndex, __EarliestIndex)

[Anonymous]

Thanks @Greg_Deckler it works perfectly !