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?
First, the best way to implement this logic is to use a measure, not a calculated column. To do this, create a separate date table and a relationship from Stock[Date] to Date[Date].
Use this formula to create a measure:
CumulativeStock = VAR MaxDate = MAX('Date'[Date]) VAR InitialDate = MAXX(FILTER(ALL(Stock);Stock[Date]<=MaxDate && Stock[Initial Inventory]="Y");Stock[Date]) RETURN CALCULATE(SUM(Stock[Quantity]);ALL('Date');'Date'[Date]>=InitialDate && 'Date'[Date]<=MaxDate)
In this formula, the variable MaxDate contains the last selected date. The variable InitialDate determines the last initial inventory date before the last selected date. After this, the measure calculates the total quantity of stock lines between the initial date and the max date.
For your sample data, this results in something like:
please try calculated measure with expression:
Stock Cummulative = CALCULATE(sum(Stock[Quantity]),filter(ALL(Dates), Dates[Date].[Year] = MAX(Dates[Date].[Year]) && Dates[Date]<= MAX(Dates[Date]) ))
To understand more about cummulative with time, you could refer topic: http://www.daxpatterns.com/time-patterns/
If this works for you please accept it as solution and also like to give KUDOS.
@tringuyenminh92 I need a calculated column (also because I need to recreate a historical column and othe calculations over it of the stock entity by product). I read that page before this post, but it doesn't consider the complexity of my case (multiple products, conditional initial inventory...)
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.
I created the column and the measure, put them ina table filtered by one product I chose: CumulativeStockColumn works better than Cumulative stock, but not totally right for now (implemented dax in the pic below). As you can see Initial Inventory is "Y" when =100.
Can you solve it? (01/01/2013 should be reset to 9640)
I don't have the field "time" but I have the movement ID wich has a chronological increase to be use for comparison, how can I insert it in the DAX formula of the measure and the column?
What about the measure CumulativeStock? I can't figure why it has those too big results.
Thank you very much