Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Reddyp
Helper I
Helper I

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 2
amitchandak
Super User
Super User

@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])))

@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)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors