Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

 

Naamloos.png

 

Thanks in advance!

 

Tyson

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
V-lianl-msft
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.