cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Average - Inventory

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

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?

Best regards:

Csakalila84

2 REPLIES 2
Super User

@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?

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

New Member

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors