Forum Discussion
Cumulative that reset
Hey everyone,
I need to sum a cumulative way that will stop at some point and reset the sum:
Example:
Product idorder repeat new column *
Product A 1 0
Product B 1 0
Product A 2 1 1
Product A 3 1 2
Product C 3 0
Product C 4 1 1
Product A 5 0
Product A 6 1 1
So if the same products repeat in the next id order it wil give 1 and if repeats again 2... But if the next one the product is not there and repeats again, the cumulative will reset and start over.
I try this one but only generetas cumulative by the sequence, i don't know how to reset:
CALCULATE(sum(m[repeat]),FILTER(m,m[product]=EARLIER(m[product])),m[idorder] <=EARLIER(m[idorder]))
13 Replies
Hi,
Here’s the DAX formula you can use:
NewColumn = VAR CurrentProduct = m[Product] VAR CurrentOrder = m[idorder] VAR PreviousOrder = CALCULATE( MAX(m[idorder]), FILTER( m, m[Product] = CurrentProduct && m[idorder] < CurrentOrder ) ) VAR PreviousValue = CALCULATE( MAX(m[NewColumn]), FILTER( m, m[Product] = CurrentProduct && m[idorder] = PreviousOrder ) ) RETURN IF( ISBLANK(PreviousOrder), 0, IF( ISBLANK(PreviousValue), 1, PreviousValue + 1 ) )it doesn't work the New Column does not exist yet so the MAX can't have it, i'm trying to create this new column
So if the same products repeat in the next id orderThat's too vague. Did you mean to say "if any product repeats across orders"? What if order 2 had product C as well?
