topic Re: New opening inventory in DAX Commands and Tips

New opening inventory

NewBIe2013 — Wed, 29 Nov 2023 19:28:36 GMT

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.

Re: New opening inventory

Jihwan_Kim — Wed, 29 Nov 2023 20:00:09 GMT

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 )

Re: New opening inventory

Fowmy — Wed, 29 Nov 2023 20:19:00 GMT

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.

Re: New opening inventory

NewBIe2013 — Thu, 30 Nov 2023 03:15:23 GMT

Jihwan_Kim thanks much, that's what I need.