calculation based on the previous value

Hi ybyb23 

What do you mean by "DAX function"? In DAX there are no functions. Either measures or calculated columns/tables. The above can be done in DAX as a measure because even though the problem in nature is recursive (and DAX does not support such constructs, bar the special kind called "side recursion"), a formula can be crafted that'll be fully iterative. If you need a table with a calculated column, please use Power Query for this.

Here's how to do it in Power Query:

 

// T
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTK0ABIGSrE6IBEjAyAHiPSMYAKGUAFjmIARVMAEJmAMFTCFCZhABcyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Kpi = _t, Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Kpi", Int64.Type}, {"Factor", type number}}),
    ReplicateKpiDown = Table.FillDown(#"Changed Type",{"Kpi"}),
    AddIndex = Table.AddIndexColumn(ReplicateKpiDown, "Index", 0, 1, Int64.Type),
    MoveIndexToFirstColumn = Table.ReorderColumns(AddIndex,{"Index", "Year", "Kpi", "Factor"}),
    AddCofactor = Table.AddColumn(MoveIndexToFirstColumn, "Cofactor", each 1 - [Factor]),
    MakeFinalCalculation = Table.AddColumn(AddCofactor, "Final", each [Kpi] * List.Product(List.FirstN(AddCofactor[Cofactor], [Index] + 1)))
in
    MakeFinalCalculation

 

I'll do it in DAX as well and then paste it here. Bear with me...

Here's the outcome of the calculation in PQ and DAX:

The file where this is done has been attached...

 

DAX certainly has functions (e.g. MAX, PRODUCTX, CALCULATETABLE). It's just that the user cannot define functions, except in limited sorts of ways.

 

Also note that while DAX cannot do recursion, the M language can. This means that instead of an O(N^2) solution that's required in DAX, we can get much better performance for large tables with an O(N) solution that uses recursion. Using List.Accumulate or List.Generate is a common way to implement recursive logic. For example,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjLCQAgDATRXvYcJB8VrSWk/zY0h70MzMuEq10I7PwoSlpc//QPpxglKE6ZlKAsyqRsVD0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text)) in type table [Year = _t, Kpi = _t, Factor = _t]),
    ChangeType = Table.TransformColumnTypes(Source, {{"Year", Int64.Type}, {"Kpi", Int64.Type}, {"Factor", type number}}),
    NewColumn = List.Generate(
        () => [x = ChangeType[Kpi]{0}, f = List.Buffer(ChangeType[Factor])],
        each not List.IsEmpty([f]),
        each [
            x = [x] * (1 - List.First(f)),
            f = List.RemoveFirstN([f], 1)
        ],
        each [x]
    ),
    AddColToTable = Table.FromColumns(Table.ToColumns(ChangeType) & {NewColumn}, Table.ColumnNames(ChangeType) & {"Final"})
in
    AddColToTable