topic Re: Calculate Inventory Turns in Developer

Calculate Inventory Turns

jausting — Tue, 17 Apr 2018 09:09:32 GMT

I am trying to calculate a rolling inventory turn for the past 12 months through direct query.

The user can select the ending period by either using a selector YEAR, then MONTH. So I could select year 2018, then month 01. So I would want to calculate the inventory turns using EOM_STK table, field - SALES. This would be from Feb-17 to Jan 18.

So I calculate stock turns as Sales for past 12 months, divided by EOM_STK( Stock on Hand)

So the user selects Jan-18 and I would plot inventory turn for Jan-18 by using total sales of Feb-17 to Jan-18 divided by EOM stock on hand for Jan-18

I would like to plot a line graph for the past 12 months of the inventory turns each month

So for example

Jan-18 3.1

Dec-17 3.2

Nov-17 3.0

...

FEB=17 2.8

I have looked at some examples using calculate, however, the key words, DATEADD, PREVIOUSYEAR & others eithe cannot be used or not valid.

Obviously, I would want to control here is actually 12 months of data once the user has selected the ending period and return an error if not.

Can anybody help ?

Thanks in advance

Re: Calculate Inventory Turns

v-chuncz-msft — Wed, 18 Apr 2018 09:14:18 GMT

@jausting,

You may take a look at the following post.

http://community.powerbi.com/t5/Desktop/Report-on-Current-Calendar-Month-Excluding-Today/m-p/346431#M155473

Re: Calculate Inventory Turns

jausting — Wed, 18 Apr 2018 23:39:27 GMT

Thanks for your response Sam.

I used DATEDIFF in my measure to allow segmententation of the information I require. I have to add a filter so that perioddate always has 12 months of data to use, otherwise the stock turn figure will be incorrect.

These are the measures I used

StockSales = CALCULATE ( SUM ( 'EOM_STK'[Sales] ), DATEDIFF(EOM_STK[perioddate], EOM_STK[perioddate],MONTH) <= 12)

StockTurns = DIVIDE(EOM_STK[StockSales],sum(EOM_STK[SOH]))

Appreciate your reponse.

Re: Calculate Inventory Turns

jausting — Tue, 24 Apr 2018 02:30:48 GMT

Actually StockSales is incorrect.

How do I get the sum of last 12 months sales when I select a month and year from a slicer. So I select 2018 / 03?

Re: Calculate Inventory Turns

jausting — Tue, 24 Apr 2018 10:53:51 GMT

Does anybody know how to resolve this ?