cumulative total by product and date

[sy898661]

hi!!

I have been looking through a few different forum posts related to this but havent really come across one that has the same issue im having.

Here is what my data looks like:

I found the cumulative total for a few differnt products sorted by date using this code I found from another post:

Cumulative Actual Qty = 
    CALCULATE(
        SUMX(Oracle,Oracle[Actual Lot Qty]),
            FILTER('Oracle',Oracle[Product] = EARLIER(Oracle[Product]) && 'Oracle'[Index] <= EARLIER(Oracle[Index])))

So my issue... when I go to plot this in a column chart, everything looks fine until it hits a month that has multiple rows of data (highlighted in the picture)... when this happens, it sums all the cumulative values for that month, so for example:

Product A, May-19 would ideally have a column value of 8, but it shows 26 instead.

How would I write a column that says: look at the last Cumulative value for each month, for each product?

It is sorted by index, would that have something to do with it?

Thanks in advance for the help! 🙂

EDIT: Added a column for desired result to better explain what I am looking for:

[HotChilli · Accepted solution]

I think if you change the aggregation in the Values well from SUM to MAX it will work. (of Cumulative Actual Qty)

As long as the cumulative quantity increases through the month. (i.e. no negatives in actual quantity)

[sy898661]

hey @HotChilli thanks for the quick response!

I tried this:

Cumulative Amount = 
    CALCULATE(
        MAX(OBIEE[Actual Lot Qty (g)]),
            FILTER('OBIEE',OBIEE[Manufacturing Product Planner] = EARLIER(OBIEE[Manufacturing Product Planner]) && 'OBIEE'[Index] <= EARLIER(OBIEE[Index])))

But that didnt add up the values correctly

...

Then I tried this:

Cumulative Actual Grams = 
    CALCULATE(
        MAX(OBIEE[Cumulative Amount]),
            FILTER('OBIEE',OBIEE[Manufacturing Product Planner] = EARLIER(OBIEE[Manufacturing Product Planner]) && 'OBIEE'[Index] <= EARLIER(OBIEE[Index])))

but its just copying the same values over

thoughts??

I added a photo with desired results into the OG question

[HotChilli]

There's a slight misunderstanding.  The original cumulative column is absolutely fine (for its purpose).

You asked about a column chart so I suggested a method to make the column chart work rather than give you a formula for another column.

Why don't you try the previous suggestion and let me know how you get on.

[sy898661]

OH! Omg, I did completely misunderstand that.

Okay, that worked absolutely perfectly. Thank you so so much!!!! 🙂