Average stock through movements

[legrand]

Hi,

after reading hours of other threads and not coming to a conclusion I have to open yet another inventory thread.

I got a table with stock movements :

04.10.2017	56.
09.10.2017	34.
10.10.2017	-1.
10.10.2017	-13.
10.10.2017	-1.
10.10.2017	-1.
12.10.2017	-1.
12.10.2017	-1.
12.10.2017	-1.
12.10.2017	-1.

and so on. I uploaded the full sample table here. Maybe I'm wrong but I think the most precise way to calculate the average stock would be to create a calculation like this:

01.10 - 03.10. = 3 days with 0 stock

04.10. - 08.10. = 4 day with 56 stock

09.10. = 1 day with 90 stock

10.10. - 11.10. = 2 day with 84 stock

12.10. = 1 day with 4 stock

...and so on divided by the days after the article was put into stock.

I hope, this seems logical. Is it possible to create a measure of the average stock for the whole time (several months or years) after the article initially was put into stock?

I also got a datetable relationship and a article table relationship. The measure has to work when it's grouped by article.

Many thanks!

[Phil_Seamark]

Hi @legrand

Would it be possible to show the expected outcome?  Feel free to use EXCEL and share the excel formula.  This will help port to DAX.

[legrand]

Hi Phil,

i hope this helps. I didn't find a more elegant way than creating a separate column for each article.

Many thanks!

[Phil_Seamark · Accepted solution]

HI @legrand

I think this is getting pretty close to your spreadsheet.

I created a new calculated table that I could add missing dates and apply a running total to.

New Table = 
VAR Date1 = GENERATE(CALENDAR(DATE(2017,10,1),DATE(2017,12,1)),VALUES('Table1'[Article]))
VAR Step1 = SUMMARIZECOLUMNS('Table1'[Article],'Table1'[Date],"Q",SUM(Table1[Quantity]))
VAR Step2 = NATURALLEFTOUTERJOIN(SELECTCOLUMNS(Date1,"Date",[Date]+0,"Article",[Article]),SELECTCOLUMNS(Step1,"Date",[Date]+0,"Article",[Article],"Quantity",[Q]))
RETURN  ADDCOLUMNS(Step2,"Column to Average",VAR s = CALCULATE(SUM('Table1'[Quantity]),FILTER('Table1',[Date]<=EARLIER([Date]) && [Article] = EARLIER([Article])))return if(s=BLANK(),0,s))

This gave me a column that I could easily average using a visual

.

Here is a link to a PBIX file that contains your data, my code and the visual

https://1drv.ms/u/s!AtDlC2rep7a-oX7_Ag9RNSNZVZnA

[legrand]

Hi Phil,

many thanks, didn't have time to test your solution until now...

I tried to transfer your solution to my data but I'm running into a potential error resulting in showing the same strange value in all rows. Maybe you can spot the mistake!

Best

Henry