cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AGo Member
Member

Stock cumulative total DAX

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.

Cattura.JPG

Someone can please help me?

21 REPLIES 21
Michiel Member
Member

Re: Stock cumulative total DAX

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:

Capture.PNG

tringuyenminh92 New Contributor
New Contributor

Re: Stock cumulative total DAX

Hi @AGo,

 

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

2016-12-14_16h27_19.png

 

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.

Best regards
Tri Nguyen

AGo Member
Member

Re: Stock cumulative total DAX

Hi @Michiel,

It is not working, I need a calculated column, and it returns wrong and big results (maybe it consider also other products)

tringuyenminh92 New Contributor
New Contributor

Re: Stock cumulative total DAX

Hi @AGo,

 

Did you use the Date column of Dates table or Date column in your fact table?

AGo Member
Member

Re: Stock cumulative total DAX

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

AGo Member
Member

Re: Stock cumulative total DAX

Hi @tringuyenminh92,

I tried both, in one case the numbers were too big, in the other it was a mere copy of the quantity column.

Michiel Member
Member

Re: Stock cumulative total DAX

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.

tringuyenminh92 New Contributor
New Contributor

Re: Stock cumulative total DAX

Hi @AGo,

 

please try @Michiel's solution, in case it's not working with your expectation, i will try another expression with calculated column.

AGo Member
Member

Re: Stock cumulative total DAX

Thank you @Michiel and @tringuyenminh92

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

 

 Screenshot 2016-12-14 14.37.15.jpg