calculation based on the previous value

[ybyb23]

Hi all, 

I am stuck with creating a dax function, i really need help.. 

Here is an example, the final in the table should be my result, I have a value for 2019 which is the starting point of the calculation, then its about adding up the previous value calculated multiplied by the factor. 

Thanks in advance for your support. 

[daXtreme · Accepted solution]

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...

[daXtreme · Accepted solution]

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...

[AlexisOlson · Accepted solution]

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

[ybyb23]

Thanks for the solution! It worked perfectly. 

I can understand it is important to define precisly the issue, however a function can be an expression, or an operation, as far as I know this is what is about in Dax. I think it's a valid point to highlight it in this forum community by creating a power bi glossery. 

I have finally opted to use Python to create the recursive logic. 

[daXtreme]

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

@AlexisOlson, when I said "functions," I meant user-defined functions, obviously, because this is what @ybyb23 asked for. They do not exist in DAX, not even "in limited sort of ways." Measures can't be considered functions, either, in any way because they can't take arguments, at least directly. But if you want to abuse terminology... well, yes, you can name any object you want anything you want. Nobody can prevent you from this.

"Also note that while DAX cannot do recursion, the M language can."

@AlexisOlson, have you read what I wrote there above? I did write that even if in DAX there's only side-recursion allowed, full recursion exists in M.

[AlexisOlson]

Saying "In DAX there are no functions" is not accurate, so I clarified this meant user-defined functions. As you said, measures cannot take arguments directly. However, you can pass filters to a measure that behave like arguments. This is limited by the fact that the domain of the function you're recreating must already be defined in the data model (you can't have arbitrary inputs; it must come from values in an existing table).

As a simple example of a limited function, let T be a table with a column [x]. Define the measure [x^2] as

VAR _x = SELECTEDVALUE ( T[x] )
RETURN _x * _x

We can now use this measure as a function so long as the input exists in column T[x]. For example,

10^2 = [x^2](T[x] = 10)
     = CALCULATE ( [x^2], T[x] = 10 )

For Power Query, I'm not claiming you said anything wrong either. The M code you wrote works fine with the caveat that it's using what you call "side recursion" the same way that's necessary for DAX (which isn't as efficient for large tables). If you're going to use M for the custom column, then I think it makes sense to show a recursive solution too, which is what I provided.

I'm not trying to pick a fight. I kudoed your post and it was accepted as the answer, as appropriate. Maybe mine will help a future reader who needs a recursive solution for performance reasons.

[daXtreme]

@AlexisOlson 

I get that. No hurt feelings 🙂 I appreciate your solution as well. Actually, I have saved this page to have something to return to when I'll need an efficient algorithm for the calculation of running totals. I did know about this way of writing M but was too lazy to implement it, so I went for the less efficient solution. Depending on how big the dataset is, it might do the trick. In case it's too slow, your code should do the trick. My code is probably easier to understand for the people new to Power BI or Power Query. Yours is more advanced. Much more.

Whether DAX has or not any kind of udf's is open to debate but it's not worth debating it. So, let's leave it at that.

Cheers!

[amitchandak]

@ybyb23 , I doubt this can be done. the recursive calculation is a little bit of a challenge in DAX. As long as we can achieve them using cumulative, that can be done