topic Re: Inventory turnover ratio in DAX Commands and Tips

Inventory turnover ratio

IvanS — Tue, 03 Jan 2023 12:11:12 GMT

Hi guys,

I found some posts about calculation of Inventory Turnover ratio (ITR) but unfortunately none of them have solution posted. Therefore I would like to have one post with the final answer to help also other users.

Here is the business need - to calculate Inventory Turnover ratio on monthly, quarterly and yearly basis & per product & per warehouse. We want to also calculate ITR for all months/quarters/years so we can visualize the trends.

Tables available:

1) Monthly Item Inventory (purpose of this table is to store the inventory figures at the end of each month) with following columns:

Date (31.12.2022, 30.11.2022 etc)
Product ID
Warehouse ID
Quantity
Value of stock

2) Item Ledger (this table captures all items of Items - purchases, sales, internal transfers) with following columns:

Date (date of transation)
Transaction Type (Sale, Purchase, Transfer)
Product ID
Warehouse ID
Quantity
Price
Value of transacion (= Quantity * Price)

Calculation of Inventory Turnover ratio - formula for ITR is following:

ITR = Cost of Goods sold / Average Inventory
Average Inventory = ( Value of stock at start of period + Value of stock at end of period ) / 2

Cost of Goods sold = sum of values of transaction (where transfer type = "Sale") in specific period (see below)

Coming back to the business need, we need to calculate Inventory Turonver ratio of several time period:

Monthly - in this case we want to calculate ITR for previous calendar month and calculation will look like:
Sum of values of transaction (sales) sold from 1.12.2022 to 31.12.2022
Average Inventory = ( Value of stock at 30.11.2022 + Value of stock at 31.12.2022 ) / 2

The same logic applies for month-2, month-3, month-4 etc.
Quarterly - in this case we want to calculate ITR for previous calendar quarter (Q4/2022) and calculation will look like:
Sum of values of transaction (sales) sold from 1.10.2022 to 31.12.2022
Average Inventory = ( Value of stock at 30.9.2022 + Value of stock at 31.12.2022 ) / 2
The same logic applies for Q3/2022, Q2/2022, Q1/2022, Q4/2021 etc.
Yearly - in this case we want to calculate ITR for previous calendar year and calculation will look like:
Sum of values of transaction (sales) sold from 1.1.2022 to 31.12.2022
Average Inventory = ( Value of stock at 31.12.2021 + Value of stock at 31.12.2022 ) / 2
The same logic applies for years 2021, 2020 etc

Thank you for any inputs. I tried to be as specific as possible but if you have any questions, just let me know.

IvanS

Re: Inventory turnover ratio

amitchandak — Tue, 03 Jan 2023 16:31:13 GMT

IvanS , check if isinscope and closingbalancemonth and openingbalancemonth

Power Bi DAX Functions openingbalancemonth, openingbalancequarter, openingbalanceyear, firstnonblankvalue, and parallelperiod. Opening Stock, First value of period: https://youtu.be/6lzYOXI5wfo

Power BI Allocating Targets- closingbalancemonth, closingbalancequarter, closingbalanceyear:https://youtu.be/yPQ9UV37LOU

use isinscope to switch between month, qtr, and year

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Re: Inventory turnover ratio

IvanS — Sat, 07 Jan 2023 15:21:26 GMT

Hi amitchandak ,

Many thanks for your suggestions.

For monthly % change, I have manage to calculate the average inventory by calculating Inventory at the end of last month and one month before the last month - please see below:

Inventory Current Month = CLOSINGBALANCEMONTH ( SUM (nav_FACT_MonthlyItemInventory[Total_Value] ), nav_FACT_MonthlyItemInventory[Date]) Inventory Previous Month = CLOSINGBALANCEMONTH( SUM (nav_FACT_MonthlyItemInventory[Total_Value] ), DATEADD(nav_FACT_MonthlyItemInventory[Date], -1, MONTH))

However, I am having issues with calculating the sum of "Sale" transations from Item ledger so I get the numbers for each month. For some reason, the CLOSINGBALANCEMONTH is working only for months which have transaction on last day of month.

Could you please provide your thoughts on how to create DAX function on this?

Thank you

IvanS