topic Re: Inventory Value end month per Warehouse in DAX Commands and Tips

Inventory Value end month per Warehouse

Anonymous — Mon, 24 Oct 2022 09:36:56 GMT

Hi
I want to have the Inventory value per end of each month per warehouse and per Materialcode.
This is an example of the data i have:

Thank you in advance!

Re: Inventory Value end month per Warehouse

Jihwan_Kim — Mon, 24 Oct 2022 10:18:29 GMT

Hi,

I am not sure whether I understood your question correctly, but I tried to create a sample pbix file like below.

Please check if it is suitable for your datamodel.

last nonblankdate: = VAR _lastdate = LASTNONBLANK ( 'Calendar'[Date], CALCULATE ( SUM ( Data[New Inventory] ) ) ) VAR _lastinventory = CALCULATE ( SUM ( Data[New Inventory] ), 'Calendar'[Date] = _lastdate ) RETURN _lastinventory

Re: Inventory Value end month per Warehouse

Waldo35 — Mon, 24 Oct 2022 10:20:10 GMT

I am gonna assume you don't need a running total here because the value is new inventory.

Here is my dataset

I generated roughly 7 months. And here are the steps i take, please note if u use a calendar table you should do that in there, not in the table itself.

1. In power query i extract the year and month from the date field, I do this by duplicating the date field and extracting the year. In a calendar table you would use MONTH and YEAR to extract these. But in this example i will use Power Query. I made 2 duplicates (right click date column and duplicate, then in transform, choose date, year, year) (Do the same to the other column but go for Month).

Now apply the changes and go back to the report editor.

2. Right click year and Create Hierarchy. After that add the month field to the hierarchy, rightclick add to hierarchy

3. Add the year and month to a table with as seperate columns from the hierarchy and then add the value field, you now have a total for the value per month.

Now the splitting into category part

I added a column category to my dataset. Which contains either 1 or 2.

Now all i have to do is just throw the category in there. And there view of Year and Month, categorized:

Re: Inventory Value end month per Warehouse

Anonymous — Mon, 24 Oct 2022 11:10:32 GMT

Hi Jihwan Kim
Thank you for your effort!
My data contains inventory value per date. For example :
In Warehouse " NLMA3.0-2" on 03-10-2022 6 pieces in stock
In Warehouse "NLMA3.0-2" on 11-10-2022 5 pieces in stock
In Warehouse "NLMA3.0-2" on 18-10-2022 4 pieces in stock
De last value in Oktober for this article is 18-10-2022. So the value should be: 4 pieces on end of Oktober. Now it gives the som of these three value moments.

Kind regards.

Walid

Re: Inventory Value end month per Warehouse

Anonymous — Mon, 24 Oct 2022 11:13:33 GMT

Hi Waldo,
Thank you for your effort!
I have already the stock value per date. The specific value of the last date of the month is the value for the end of the month.
Kind regards.

Walid

Re: Inventory Value end month per Warehouse

Anonymous — Tue, 25 Oct 2022 07:13:44 GMT

Hi Anonymous

Did you get the result you want ? If the above replies are helpful to you , please consider Accept it as the solution to help the other members find it more quickly .

Best Regards,
Community Support Team _ Ailsa Tao

Re: Inventory Value end month per Warehouse

Anonymous — Tue, 25 Oct 2022 07:57:17 GMT

Hi Ailsa Tao,

It did not worked yet.

Kind regards.

Walid