Stock cumulative total DAX

[AGo]

Hi! I'm trying to DAX a calculated column for the stock cumulative total by product. When present the first movement of the year of a product has Initial Inventory="Y" so the cumulative calculation resets and begins from that quantity value, if there's not a Initial inventory movement the calculation won't reset, how can I write this formula?

Another problem is that I could have multiple movements of the same product in the same date with the same moved quantity, I have to use a row index number logic?

Consider that this database has several years of data.

Someone can please help me?

[Michiel]

The formula doesn't work as a calculated column because of the different context (it will be calculated in the context of the current row only, that's why you get the amount itself).

Try this one as a calculated column:

CumulativeStockColumn = 
VAR MaxDate = [Date]
VAR CurrentProduct = [Product]
VAR InitialDate = MAXX(FILTER(ALL(Stock);Stock[Date]<=MaxDate && Stock[Product]=CurrentProduct &&Stock[Initial Inventory]="Y");Stock[Date])
RETURN
   SUMX(FILTER(ALL(Stock);Stock[Date]<= MaxDate && Stock[Date]>=InitialDate && Stock[Product] = CurrentProduct); Stock[Quantity])

The calculation is based on comparing the dates, so transactions on the same day will have the same cumulative result; unless there's a time component in the date as well.