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?

! Edited reply, first solution had an error ! So we can modify the query like this: let Function = (Table) => let Sort = Table.Buffer(Table.Sort(Table,{{"MovementID", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(Sort, "Index", 1, 1), Offset = Table.AddColumn(#"Added Index", "Deduct", each if [Initial Inventory if equals 100]=100 then List.Sum(Table.SelectRows(#"Added Index", (this) => this[Index] < [Index])[Quantity]) else null), #"Filled Down" = Table.FillDown(Offset,{"Deduct"}), #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,0,Replacer.ReplaceValue,{"Deduct"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom.1", each List.Sum(List.FirstN(#"Replaced Value"[Quantity], [Index]))-[Deduct]) in #"Added Custom", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFLDoAgDETvwtqYFvG3VI9BuP81dKrEtn4SHpt5SemQcyBujxOJh9AEBiQX7iWUxhkR0KW+5B2o+frME7gnbKcRldEDN0Hngpug8xHUfLMvQDaBzx1hzOBjx9E29LKlGFIku1d02kBR7Js0RhLNTTFGL9rPrhylzEff1UEX58cTmUrKDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, MovementID = _t, Quantity = _t, #"Initial Inventory if equals 100" = _t, Product = _t]), ChgType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"MovementID", Int64.Type}, {"Quantity", Int64.Type}, {"Product", type text}, {"Initial Inventory if equals 100", Int64.Type}}), Group = Table.Buffer(Table.Group(ChgType, {"Product"}, {{"All", each _, type table}})), #"Added Custom" = Table.AddColumn(Group, "Custom", each Function([All])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "MovementID", "Quantity", "Initial Inventory if equals 100", "Product", "Index", "Deduct", "Custom.1"}, {"Date", "MovementID", "Quantity", "Initial Inventory if equals 100", "Product", "Index", "Deduct", "RunningTotal"}) in #"Expanded Custom" Probably not ideal any more, but still not sure what your desired result is. So pls check if this is your desired result.The file you've sent is still connected to your source and therefore not showing the data in the preview.Also it doesn't contain the movementID, which is crucial for my solution to work! If thats your result, pls check performance and come back if it is too slow - there is some room for optimization in the above query - or we hand it to the DAX-experts, who might transform performance to different level :-)

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:

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

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

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

Stock cumulative total DAX | Microsoft Fabric Community

21 Replies

Michiel
Resolver III
9 years ago
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:
- AGo
  Post Patron
  9 years ago
  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
    Memorable Member
    9 years ago
    Hi AGo,
    
    Did you use the Date column of Dates table or Date column in your fact table?
tringuyenminh92
Memorable Member
9 years ago
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]) ))

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
  Post Patron
  9 years ago
  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...)
  - tringuyenminh92
    Memorable Member
    9 years ago
    Hi AGo,
    
    please try Michiel's solution, in case it's not working with your expectation, i will try another expression with calculated column.

Forum Discussion

Stock cumulative total DAX

21 Replies

Related content

Cumulative Total

Cumulative Total

SumX? - cumulative running total

Cumulative Total

Help to create stock inventory forecast (cumulative/running total)