Inventory turnover by month

[Anonymous]

Hello guys,

I need some hep, I m stuck. I try to create an monthly inventory turnover and monthly change %.

Date	Amount	Balance
11/01/2016	100	100
12/01/2016	200	300
01/01/2017	50	350
02/01/2017	100	450
03/01/2017	500	950
04/01/2017	100	1050

I've managed to created Balance column.

Balance = CALCULATE( SUM(Table[CostAmountTotal]), FILTER(  ALLSELECTED(Table[Period]),  ISONORAFTER(Table[Period], MAX(Table[Period]), DESC)))

But I'm not able to calculate monthly change %.I don't know how to make an reference to previous period.

Any  hints are usefull

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

Assume the same data has two columns Date and Amount as you mentioned, we can create below calculated columns: 

Balance = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))

MonthlyChange% = var premonth=CALCULATE(SUM('Table'[Amount]),FILTER('Table',EARLIER('Table'[Date])=DATEADD('Table'[Date],1,MONTH)))
return 
DIVIDE(CALCULATE(SUM('Table'[Amount])-premonth),premonth)

Best Regards,
Qiuyun Yu 

[Anonymous]

thanks.

EARLIER function is very good.

[Anonymous]

@v-qiuyu-msft

Solution is working well, but I have a small problem.

The table is a summarized tabled calculated from a lot of transactions using summarize function.

In detail table I have a column , Category.

How to make possible to filterby this column and summarized table to be filtered ?

[Joorge_C]

You will need something like:

DATEADD(Calendar[Date},-1MONTH)

That -1 MONTH will be the interval to substract so you can get to the previous period of whatever Calendar[Date] you give.