Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
@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...)
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
Hi @AGo,
I think i got your expectation. As my understand, you want to show comparison the initial inventory with cummulative of sale quantity (available stocks).
Dates = CALENDARAUTO()
Year = YEAR(data[Date])
Init inventory = CALCULATE(sum(data[Quantity]),FILTER(data,data[Initial Inventory]="Y" && data[ProductId]=EARLIER(data[ProductId]) && data[Year]=EARLIER(data[Year]) ))
Cumm = CALCULATE(sum(data[Quantity]),filter(all(Dates), Dates[Date] <= MAX(Dates[Date]) && Dates[Date].[Year] = max(Dates[Date].[Year]) ))
(The cummulative has condition: Year= max(year) to ensure cummulative happens for each year as Time Pattern in topic: http://www.daxpatterns.com/time-patterns/)
Please notice that i use Date column of Dates table in showing fields.
Sample: https://www.dropbox.com/s/d07if69oxy7kuqw/Initial_Cummulative.pbix?dl=0
Data: https://www.dropbox.com/s/beqdng6ix8ws89e/InitialInventory_Cummulative.xlsx?dl=0
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
Hi @tringuyenminh92,
I'm sorry but it's not working yet, the result is like a mere copy of the quantity column.
Hope to reach a solution.
Hi @tringuyenminh92,
I added the relationship but the result is the same: it's like a copy of quantity and it has a result only for the last year.
Hi @CheenuSing, @ImkeF, we need your help in this case cause i'm confusing and have no solution so far.
currently very busy, can have a look at it after Christmas.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @CheenuSing, @ImkeF, @tringuyenminh92 , did you figure out some solution for this problem? Many thanks
Hi @AGo,
Had dinner earlier and nothing to do tonight so I observed your sample file, but still stuck in figuring out way to cummulative in period by Inital column with DAX 😞 maybe you could try ImkeF's approach
Sorry, totally forgot about this.
Pls paste this M-code into the advanced editor in the query editor to check if this are the results which you would expect.
The code contains sample data that hopefully makes it possible to check the calculation logic.
let Function = (Table) => let Sort = Table.Sort(Table,{{"MovementID", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(Sort, "Index", 1, 1), Year = Table.AddColumn(#"Added Index", "Year", each Date.Year([Date])), #"Grouped Rows" = Table.Group(Year, {"Product", "Year"}, {{"All", each Table.FillDown(_,{"InitialInventory"}), type table}}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"All"}), #"Expanded All" = Table.ExpandTableColumn(#"Removed Other Columns", "All", {"Date", "MovementID", "Quantity", "InitialInventory", "Product", "Index", "Year"}, {"Date", "MovementID", "Quantity", "InitialInventory", "Product", "Index", "Year"}), Offset = Table.AddColumn(#"Expanded All", "Custom", each List.Sum(Table.SelectRows(#"Expanded All", (this) => this[Year] < [Year])[Quantity])), ReplNull = Table.Buffer(Table.ReplaceValue(Offset,null,0,Replacer.ReplaceValue,{"Custom"})), #"Added Custom" = Table.AddColumn(ReplNull, "Custom.1", each if [InitialInventory]<> null then List.Sum(List.FirstN(ReplNull[Quantity], [Index]))-[Custom]+[InitialInventory] else List.Sum(List.FirstN(ReplNull[Quantity], [Index]))) in #"Added Custom", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBRDoAgDEPvwjcxDFD0UzkG4f7X0E4SqZDw+tNmoyvFOFme551sxhoBTgV6mmp/CQ/gze0Amn2NdgROR0Dzm/BdYgU8v7cVnt/bCTQ783pYO2j1Jv0QOcC8X+LjjA01oCcU/kPoA16FV1AgqPAKCkSVb0W9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, MovementID = _t, Quantity = _t, InitialInventory = _t, Product = _t]), ChgType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"MovementID", Int64.Type}, {"Quantity", Int64.Type}, {"InitialInventory", Int64.Type}, {"Product", type text}}), 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", "InitialInventory", "Product", "Custom.1"}, {"Date", "MovementID", "Quantity", "InitialInventory", "Product", "Custom.1"}) in #"Expanded Custom"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
It's not correct because I have a quantity under Initial Inventory column. The column initial inventory of the source table doesn't contain a quantity but only an attribute that indicates if that row contains an Initial Inventory type of movement. Despite this, we are near to the solution, I like the calculation logics behind the custom column.
! 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 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you @ImkeF,
it seems good, now I'm trying to switch the source from json to my table in PBI, but it returns "Token literal expected".
I'm sorry I'm not good at Power Querying.
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 @Michiel,
It is not working, I need a calculated column, and it returns wrong and big results (maybe it consider also other products)
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |