cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

Hi @TGB ,

 

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

Hi @TGB ,

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors