Average - Inventory

[Csakalila84]

Hi all,

It would be much appreciated if somebody help me to find the solution to the issue below:

I have a table which includes all the inventory movements for 2016 ('2016_Inventory_BD') and contains the following relevant columns:

1. Product_ID

2. Date

3. Qty

4. Amount

The table contains opening balance, all purchased and sold quantity/Amount of every single product by date.

From the database I aimed to calculate an annual inventory rotation by product using a very simple

formula: Number of cycles = (Full Year Cost of Good Sold / Weighted Average of Inventory Value)

In order to get the Number of rotation I thought that I do the followings:

1. Create a separate 'date' table which is linked to '2016_Inventory_BD'.

DATE = ADDCOLUMNS(
 CALENDAR(MIN('2016_Inventory_DB'[Date] );MAX('2016_Inventory_DB'[Date]));
 "DateKey";FORMAT([Date];"YYYYMMDD");
 "YearNumber";YEAR([Date]);
 "MonthNumber";FORMAT([Date];"M");
 "MonthNameShort";FORMAT([Date];"mmm");
 "MonthName";FORMAT([Date];"mmmm");
 "QuarterNumber";FORMAT([Date];"Q");
    "Quarter";"Q" & FORMAT([Date];"Q");
 "YearQuarter";FORMAT([Date];"YYYY") & "/Q" & FORMAT([Date];"Q");
 "MonthDayNumber";day([Date]);
 "WeekNumber";WEEKNUM([Date])-1;
 "WeekDayNumber";WEEKDAY([Date]-1)
)

2. Create measure which shows the cumulated amount of Inventory by product:

GG_Amount_HUF_Closing_Calc_CUM = CALCULATE (
SUM ( '2016_Inventory_DB'[GG_Amount_HUF_Closing_Calc] );
     FILTER ( ALL ( 'DATE'[Date] ); 'DATE'[Date] <= MAX ( 'DATE'[Date] )))

3. Create measure which shows the cumulated amount of CoGS by product:

GG_Amount_HUF_sold_CUM = CALCULATE ( sum ( '2016_Inventory_DB'[GG_Amount_HUF_Sold] ); FILTER ( ALL ( 'DATE'[Date] ); 'DATE'[Date] <= MAX ( 'DATE'[Date] )))

Here I got stuck because I have to calculate somehow the monthly average Inventory:

I should somehow create a measure which shows the monthly AVG inventory balance by product (240651+43755+219134+153394+211047+155508+103703+30509+76230+155383+313627+200543)/12=158624

After that I should create an other measure which shows the result of the formula desired (1082604/158624=7 -> 7 rotation within a year in average)

Do you have any idea how this issue can be solved?

Thanks a lot in advance!

Best regards:

Csakalila84

[vanessafvg]

@Csakalila84

so just to be clear

1. you want a annual average or you want to know the monthly average cummulatively?  ie if its january, then just january, if its feb then feb + jan / 2 ?  ie. how are you wanting to display this?

2.  then you want to take the total average and divide by the total amount sold for the year?

[Csakalila84]

Hi,

Currently I need to have a full year review for 2016 so I would need an average of 12 months - (jan+....+dec)/12, however it would be cool if it can be applied not only for calendar year. Basically I would need a 12 month "rolling" average.

I would like to list all the products with the 12 month turnover, period end inventory balance and 12 month rolling average, and then turnover should be divided by the rolling average.

I hope that I explained it clearly.

Many thanks!

Cs.