Forum Discussion

Reddyp's avatar
Reddyp
Helper I
4 years ago

Inventory Calculation_DAX help

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

 

 

I got answer for 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)

but I have another question

Now I need help in inventory calculation(help me in modifing above DAX code)) for individual branch.
Example: the inventory deduction should be done on specific branch

 

Example: 

Date

Branch

New Stock

Stock out

Result

01-01-2022

Branch 1

10

 

0

02-01-2022

Branch 1

10

 

0

03-01-2022

Branch 1

10

 

8

04-01-2022

Branch 1

 

15

 

05-01-2022

Branch 1

 

7

 

06-01-2022

Branch 2

10

 

0

07-01-2022

Branch 2

10

 

5

08-01-2022

Branch 2

10

 

10

09-01-2022

Branch 2

 

15

 

10-01-2022

Branch 3

10

 

0

11-01-2022

Branch 3

 

10

 

 

2 Replies

  • Reddyp , With help from a common date table

     

    CALCULATE(SUM(Table[New Stock]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Out ]),filter(date,date[date] <=maxx(date,date[date])))

    • Reddyp's avatar
      Reddyp
      Helper I

      amitchandak Thanks for the reply 
      another question: Where can i fit this above mentioned DAX code into below code. the below mentioned DAX is designed to calculate inventory but now I need to update code for this old DAX measure where the deduction of inventory stock will happen based on branch name.

      Old DAX measure: 

      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)