Calculate Inventory Turns

[jausting]

Hi 

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

[v-chuncz-msft]

@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#...

[jausting]

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.

[jausting]

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?

[jausting]

Does anybody know how to resolve this ?