cancel
Showing results 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

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

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 =

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

Helper I

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 =

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors