Inflation index calculation - easy in excel but difficult in DAX

[Anonymous]

Year	Inflation	Inflation Index
2024	0.03	1
2023	0.02	0.970874
2022	0.01	0.951837
2021	0.01	0.942413
2020	0.02	0.933082
2019	0.02	0.914786

simple table, this year index is always 1

year 2023 is calculated as (2024 index / (1 + 2024 inflation)

year 2023 is calculated as (2023 index / (1 + 2023 iflation) 

very easy in excel but cannot do it in PBI as the measure references to itself

can you please help ❤️

 

[lbendlin · Accepted solution]

Better do this in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlHSUTLQMzBWitUB840hfCMY3wjCN4TxDdH4BqjqDS3h/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Inflation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inflation", Percentage.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Inflation Index", each List.Accumulate({0..[Index]},1,(state,current)=>if current=0 then state else state/(1+#"Added Index"{current-1}[Inflation])), Percentage.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

But if you want DAX as a calculated column: