Cumulative calculated column (grouped by) doesn't work.

[Anonymous]

Hi everyone,

I have a large table that contains all mutations in our warehouse. I'd like to accumulate these mutations, grouped per ProductID, in a new column (a measure is not an option due to other stuff I'd like to do afterwards).

I've managed make an accumulated column based on the ProductID and Date Value. However, this doesn't seem to be accurate. I assume this is caused by multiple mutations on the same date. Additionally I tried to add a Time based requirement. However, these also contain duplicates. 

With the current formula I retrieve the following data (SumMutations is correct, ActualStock is summed up, thus wrong. It also contains blanc spots.)

Please see the following screen pic of the desired output and the formula that I've used:

Thanks in advance!

Tyson

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, there is no unique value in your table. The repeated date and time is the reason for the repeated results.

After sorting by date time in the query editor, create an index column.

Replace the date column with the index column as the filter condition.

'XXXXX'[Index]<=_thisindex

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.