Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
2) Item Ledger (this table captures all items of Items - purchases, sales, internal transfers) with following columns:
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:
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.
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.
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
@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/
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |