Forum Discussion

Helper I

4 years ago

Solved

Inventory_stock

I need help in inventory calculation,
Example: I have received stock on 1st, 2nd, and 3rd and the stock is 10 shirts per day and total stock is 30 now
and on 4th 15 shirts sold

desired result: the sold count should deduct fromn first in date(the count sold is 15 on 4th so 10 should be decuted from 1st date and 5 shoud be deducted on 2nd)
and in the same way when 7 shirts sold on 5th date then 5 deducted from 2nd date (remaining in 2nd date) and 2 should deduct from 3rd date

Date	New Stock	Stock out	Result
01-01-2022	10		0
02-01-2022	10		0
03-01-2022	10		8
04-01-2022		15
05-01-2022		7

Simply, first out should be deducted from first in number

Need Help

Greg_Deckler

4 years ago

Reddyp Sorry about that, syntax issues:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __InTable = FILTER(ALL('Table'),[New Stock] <> BLANK())
  VAR __OutTable = FILTER(ALL('Table'),[Stock out] <> BLANK()) 
  VAR __Out = SUMX(__OutTable,[Stock out])
  VAR __ResultTable = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        __InTable,
        "__RunningTotal",SUMX(FILTER(__InTable,[Date]<=__Date),[New Stock])
      ),
      "__WhileLoop",[__RunningTotal] - __Out
    )
  VAR __Result = MAXX(FILTER(__ResultTable,[Date] = __Date),[__WhileLoop])
RETURN
  IF(__Result <= 0,0,__Result)

4 Replies

Greg_Deckler

Community Champion

4 years ago

Reddyp Try this:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __InTable = FILTER(ALL('Table',[New Stock] <> BLANK())
  VAR __OutTable = FILTER(ALL('Table',[Stock out] <> BLANK()) 
  VAR __Out = SUMX(__OutTable,[Stock out])
  VAR __ResultTable = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        __InTable,
        "__RunningTotal",SUMX(FILTER(__InTable,[Date]<=__Date),[New Stock])
      ),
      "__WhileLoop",[__RunningTotal] - __Out
    )
  VAR __Result = MAXX(FILTER(__ResultTable,[Date] = __Date),[__WhileLoop])
RETURN
  IF(__Result <= 0,0,__Result)

Reddyp

Helper I

4 years ago

Greg_Deckler Thank you for the reply, but I am facing issue while exicuting the measure
Can you please recheck or can you please guide me to know my mistake in adding measure

Greg_Deckler

Community Champion

4 years ago

Reddyp Sorry about that, syntax issues:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __InTable = FILTER(ALL('Table'),[New Stock] <> BLANK())
  VAR __OutTable = FILTER(ALL('Table'),[Stock out] <> BLANK()) 
  VAR __Out = SUMX(__OutTable,[Stock out])
  VAR __ResultTable = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        __InTable,
        "__RunningTotal",SUMX(FILTER(__InTable,[Date]<=__Date),[New Stock])
      ),
      "__WhileLoop",[__RunningTotal] - __Out
    )
  VAR __Result = MAXX(FILTER(__ResultTable,[Date] = __Date),[__WhileLoop])
RETURN
  IF(__Result <= 0,0,__Result)