Inventory Value end month per Warehouse

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

Hi Ailsa Tao,

It did not worked yet. 

Kind regards.

Walid

[Waldo35]

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:

[Anonymous]

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

[Jihwan_Kim]

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

[Anonymous]

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