Perform Calculation in three ways

[John Jairo V]

Hi to all!

I have some values in column, and I want to calculate the cumulative values with upper limit: 500.
I'd like to perform this task (using Power BI) in:

1. M (PowerQuery)
2. Calculated Column (DAX) and
3. Measure (DAX)

Just for verify how to calculate in this three states.

Check the file. Blessings!

 

[Chihiro]

This type of calculation is difficult to do in PowerBI space, as M nor DAX allows for self referencing calculation (i.e. Calculated column referencing calculation result of it's own previous rows).

Having said that, this is interesting challenge and best done in M using list function(s), specifically List.Accumulate.
I'm headed into meeting now, but will see if I have time later today to tackle this.

 

[John Jairo V]

Ok @Chihiro! I'm waiting. Thx for your answer. Blessings!

 

[Chihiro]

Ok, had bit of time this morning. Below is the custom function. Instead of using List.Accumulate, I ended up going with List.Generate.

(myVals as list) =>
let
    vCount = List.Count(myVals) + 1,
    calVals = List.Generate(()=>[x=0, y={}, z=0, Original=myVals{0}], each [x] < vCount, each [x = [x]+1,
                            y = [y] & {if x = 1 then myVals{0} else if (List.Sum(List.FirstN(myVals,x)) - List.Sum(List.Transform([y], each Number.From(_)))) > 500 then 500 else
                            (List.Sum(List.FirstN(myVals,x)) - List.Sum([y]))}, z = y{x-1}, Original=myVals{x-1}]),
    res = List.LastN(calVals, vCount -1)
in
    res

You invoke it using column from table (i.e. your values column).


This will generate list of records [x, {y}, z, Original].
y is list itself and used to hold calculated values at each stage as list for further processing. (Edited for clarity)
Original is just there to retain original value, as you can't join back invoked function result back onto source (you could, if you create copy of original and base your column on that one).

Convert the list into table and expand x, z & Original. Adjust column name(s) and position as needed.

 

[Chihiro]

Oh, just realized I forgot to remove List.Transform(). It was only used for trouble shooting some issues during building logic.
This...

List.Sum(List.Transform([y], each Number.From(_)))

Can be simplified to...

List.Sum([y])

 

[John Jairo V]

Nice! I have to break this into small parts for understanding, and I have to study functions in M code. Thx for sharing!

If you have time to check the possible solutions in DAX (calculated columns and measures), would be nice too. Blessings!

 

[Chihiro]

Measure, I can't think of way to do this type of calculation. As you really don't have context to evaluate measure in this instance.

And while it may be possible do this in calculated column... (though I can't think of method off top of my head, perhaps using column/table as variable). I'd advise against it. Have a read of thread below for explanation.
https://chandoo.org/forum/threads/dax-vs-query-which-is-faster.43535/