Cumulative that reset

[ricardo9]

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]))

[lbendlin]

So if the same products repeat in the next id order

That's too vague.   Did you mean to say "if any product repeats across orders"?  What if order 2 had product C as well?

[ricardo9]

it can't, the id order doesn't repeat the product

[lbendlin · Accepted solution]

[ricardo9]

not correct, i need to create New Column* that i put there

[lbendlin]

as I said, the requirement is too vague. What if Product C is part of idOrder 2 ?  What would be the expected result?

[ricardo9]

As i said it wont happen because de order does not accept the same product

[lbendlin]

I don't know what that means.  Both orders 1 and 3 have two products. why can order 2 not have two products?

[ricardo9]

Just can't be the same product, a order can have a lot of products

[lbendlin]

What is the expected outcome for 

Product      idorder

Product A	1
Product B	1
Product A	2
Product C	2
Product A	3
Product C	3
Product C	4
Product A	5
Product A	6

[ricardo9]

Product idorder New column

Product A 1 0
Product B 1 0
Product A 2 1
Product C 2 0
Product A 3 2
Product C 3 1
Product C 4 2
Product A 5 0
Product A 6 1

[lbendlin]

[Kaviraj11]

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

[ricardo9]

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