Forum Discussion

Anonymous

Not applicable

8 years ago

Solved

Inventory turnover by month

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
8 years ago
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
Inventory turnover by month.pbix53 KB

4 Replies

Anonymous
Not applicable
8 years ago
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.
v-qiuyu-msft
Community Support
8 years ago
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
Inventory turnover by month.pbix53 KB
- Anonymous
  Not applicable
  8 years ago
  thanks.
  EARLIER function is very good.
  - Anonymous
    Not applicable
    8 years ago
    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 ?