Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AGo
Post Patron
Post Patron

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
tringuyenminh92
Memorable Member
Memorable Member

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

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

Hi @AGo,

 

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

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

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

  • Create Dates table :
Dates = CALENDARAUTO()
  • Create Year column for fact(sales data) table:
Year = YEAR(data[Date])
  • Now i created calculated column to show Initial Inventory for all rows:
Init inventory = CALCULATE(sum(data[Quantity]),FILTER(data,data[Initial Inventory]="Y" && data[ProductId]=EARLIER(data[ProductId]) && data[Year]=EARLIER(data[Year])  ))
  • Create cummulative measure:
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/)

Screenshot 2016-12-17 20.07.28.png

 

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

Cattura.JPG

 

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 @AGo,

Please make relationship for date column of your fact table and Dates table, and check your formula in Cumm measure cause I saw something wrong in your formula.

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.

Hi @tringuyenminh92

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.

Michiel
Resolver III
Resolver III

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

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?

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.