Need help to calculate inventory turnover rate

[Anonymous]

Im new to using DAX functions, so I need a bit help with this issue.
I have got Item 4010 for this example (I have exported it to excel, but it looks simlar in PB):

Stock value should be 7374,6 - 122,91 as of today. (Yes this particular item doesnt sell very well :)) 
I want to be able to show the inventory turnover rate for the last year for this item, which should be:
COGS/((Beginning inventory+Ending inventory)*0,5).
How could I do this? Is it an idea to create an new coulmn that shows the cumulative Cost_Amount_Actual based on the item_No and the rising dates?
Appriciate any help, thanks. 

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler Sorry about that. 

We have a quantity of 59 of this item in stock today. Which have the value of 7374,6 - 122,91 = 7251,69. We had 60 in the end of may 2019, so it means that we have sold just 1 of this item since then.

To show the inventory turnover rate today for the last year it should be:
Costs of goods sold / Average inventory last year --> 122,91/((7374,6+7251,69)*0,5) = 0,0168.

So: I want to have a formula that shows all cost of goods sold (Cost_Amount _Actual with Item_Ledger_Entry type = "Sales") the last year divided by the avarage inventory value for the last year. Which in this case is the total of Cost_Amount_Actual at the start of the "year" (04.10.2019) + the total of Cost_Amount_Actual at the end of the "year" (05.10.2020). 

I hope this helps!

See the sample data in next post.

[Anonymous]

Posting_Date	Item_Ledger_Entry_Type	Cost_Amount_Actual	Invoiced_Quantity	Cost_per_Unit	Item_No
31.05.2019	Up adjustment	7374,6	60	122,91	4010
16.09.2020	Transfer	-245,82	-2	122,91	4010
16.09.2020	Transfer	245,82	2	122,91	4010
21.09.2020	Transfer	-245,82	-2	122,91	4010
21.09.2020	Transfer	245,82	2	122,91	4010
25.09.2020	Sales	-122,91	-1	122,91	4010
25.09.2020	Sales	0	0	122,91	4010

[Anonymous]

"So: I want to have a formula that shows all cost of goods sold (Cost_Amount _Actual with Item_Ledger_Entry type = "Sales") the last year divided by the avarage inventory value for the last year. Which in this case is the total of Cost_Amount_Actual at the start of the "year" (04.10.2019) + the total of Cost_Amount_Actual at the end of the "year" (05.10.2020)."

1. You have to describe how you want to exactly calculate the value of "all cost of goods sold." Saying Cost_Amount _Actual with Item_Ledger_Entry type = "Sales" might or might not be enough. This is because your table is a snapshot and you don't have entries on each day.

2. The other quantities are also undefined. What is "the total of Cost_Amount_Actual at the start of the year (04.10.2019)"? Where is the entry that tells me this for each particular item that you have in your inventory?

Please give us a good description (and a supporting clear example) of the quantities and you'll get a formula.

[Anonymous]

@Anonymous 

Thanks for your answer.

1. I think I figured this out myself. I suppose this will work:

Coulmnmeasure:

Cost of goods sold =
IF(
Itemposts[Entry_Type] = "Sales",
-Itemposts[Cost_Amount_Actual],
0
)

Measure:

COGS -1 year =
CALCULATE(
SUM(Itemposts[Cost of goods sold]),
Itemposts[Posting_Date] > TODAY() - 365)

2. 
We got a new ERP system in the start of june 2019, so all the (60) items with value 7374,6 that was in the old system was transfered to the new system on 31.05.2019. The table i posted above includes all activities for item 4010.
Heres how I think regarding the calculation of start of "year" and end of "year":

Date      |  Item No | COGS -1year | Measure = Average Inventory -1year |  Invent. Turnov. rate -1year
"Today" |   4010     |      122,91      |                        7313,5                           | 122,91/7313,15 = 0,0168

Calculation average inventory last year is as follows:
7374,6 <-- Start of "year"
7374,6-122,91 = 7251,69 <-- End of "year"
(7374,6+7251,69)'0,5 = 7313,15 <--Average last year (-365 days)

Hope this helps!

[MAAbdullah_47]

Hi @Anonymous  , totally confusing and misleading could you re-explain the solution? 

[Anonymous]

Does anyone have a solution for this?
Thanks.