Forum Discussion
Post PatronStock cumulative total DAX
Post PatronThank 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
Memorable MemberHi 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/)
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
- AGo
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.
- tringuyenminh92Hi 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.- AGo
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.