Forum Discussion
New opening inventory
Hi Bi folks, need your expertise here, I'm newbie to Power BI.
Would like to have a power bi dax to be able to achieve below result, it's relatively simple in Excel.
The below shows a forecast of demand and projected stock to receipt, having the starting inventory (500) minus the demand (100) + stock receipt (1000 ) = 1400 (this is the next open inventory of next month).
I had measures for all but just couldn't get to the new inventory (1400) for next month.
Can you please help? Thanks.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Opening inventory: = VAR _initialinventory = 500 VAR _start = CALCULATE ( MIN ( Data[Date] ), ALL ( Data[Date] ) ) RETURN SWITCH ( TRUE (), MAX ( Data[Date] ) = _start, _initialinventory, SUMX ( WINDOW ( 1, ABS, -1, REL, SUMMARIZE ( ALL ( Data ), Data[Date], Data[Demand], Data[Stock receipt] ), ORDERBY ( Data[Date], ASC ) ), Data[Stock receipt] - Data[Demand] ) + _initialinventory )
3 Replies
Jihwan_Kim thanks much, that's what I need.
- Jihwan_Kim
Super User
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Opening inventory: = VAR _initialinventory = 500 VAR _start = CALCULATE ( MIN ( Data[Date] ), ALL ( Data[Date] ) ) RETURN SWITCH ( TRUE (), MAX ( Data[Date] ) = _start, _initialinventory, SUMX ( WINDOW ( 1, ABS, -1, REL, SUMMARIZE ( ALL ( Data ), Data[Date], Data[Demand], Data[Stock receipt] ), ORDERBY ( Data[Date], ASC ) ), Data[Stock receipt] - Data[Demand] ) + _initialinventory ) - Fowmy
NewBIe2013
How does your source data look like? Sharing sample/dummy data would be helpful with the expected output. Save in Google Drive and share the link here granting permission to open.
