cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
user12
Helper I
Helper I

Track inventory level over time

Hi,

 

I am currently working on a project where I want to show the inventory level of certain items on a timeline. For this I have two lists where on holds positive values for when an item is entering the inventory and a second one holding negative values for when it is being sold/leaving the inventory. Both lists are using unique IDs to link the individual items across the lists.

 

I would like to use these two lists to show how the companies inventory is behaving over time. My prefered visual would be a line diagram showing how the stocklevel was behaving across and within years.

 

Any help here?

 

Thanks and best regards!

19 REPLIES 19
user12
Helper I
Helper I

image.png

 

user12
Helper I
Helper I

Hi,

 

The data looks roughly as follows:

 

 

This graph should show that :

On 31.12.2020 we had a stock of 1 for Bikes and 1 for Cars.

On 1.1.2021 the stock increased to a total of 3 (2 Bikes, 1 Car) 

On 15.1.2021 the stock increased to a total of 4 (3 Bikes, 1 Car)

On 16.1.2021 the stock increased to a total of 5 (3 Bikes, 2 Cars)

On 20.1.2021 the stock decreased to a total of 4 (2 Bikes, 2 Cars)

 

The layout of the data is clear to me so far. The problem starts between years. In this example, the stock on the 1.1.2021 always reads 1 Bike since the others where booked in the previous year. Any way to solve this issue? Can I create  a "YTD" that starts on i.e. 1.1.2015 and counts until today?

 

Thanks in advance and best regards

Simon

yingyinr
Community Support
Community Support

Hi @user12 ,

I have created a sample pbix file(see attachment) for you, please check whether that is what you want.

inventory level.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks a lot for your support! This has already worked out well!

I did however run into another issue:

 

I need to take the ID into account. In my list there are a couple of machines that I have no "In" (positive value) for. They do however have an "Out" (negative value). Those are machines that were put into storage before the dataset. I would like to not take these into account. Thus only "Out" (-1) values for machines where there is an "In" value.

 

Can I work this out in Power BI somehow? I have the "In" and "Out" in seperate tables that are merged together.

 

Thanks and best regards!

yingyinr
Community Support
Community Support

Hi @user12 ,

What do you mean that need to take ID into account? Could you please provide your sample pbix file(exclude sensitive data) and your expected result with example? Thank you.  Do you still want to get the count of biks/cars?

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The problem I am having is that I am trying to calculate the stock based on data starting on 1.12.2020. This means, that I do not know what machines entered the inventory prior to 1.12.2020.

 

In practice, this means that object A is leaving the inventory (-1) but on my list it has never entered the inventory (since I do not have data prior to the 1.12.2020). The inventory for this item is therefor -1 and not 0. 

 

To bypass this issue, I would like to only take those -1 values into account that already exists with a +1 value --> hence only calculate products leaving the inventory that I have registered entering the inventory. Since every booking has a unique ID, there might be a solution here.

 

I have tried to show this in the screenshot. The marked IDs have a negative value for [In/Out] but do not exist with a positive value. They should therefore not be counted in my inventory graph since they are "leaving the warehouse" but have never entered it according to my data. The graph shows the problem too. The inventory goes below zero since I am taking machines out of the warehouse but never documented them ever being in it.

 

Best regards

 

user12_0-1613125128932.png

 

I am somehow not unabled to upload pbix files

yingyinr
Community Support
Community Support

Hi @user12 ,

You can upload your pbix file to OneDrive for Business, then share the file link with me. Please find the details in the following documentation.

Share OneDrive files and folders

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Also, is it possible to formulate the Measure differently, so that applied filters still work? I would like to be able to filter by user and other criterias.
 
The formula I am refering to is this:
 
Measure =
CALCULATE (
SUM ( 'Book'[In/Out] ),
FILTER (
ALLEXCEPT ( 'Book', 'Book'[Category] ),
'Book'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
)
)
yingyinr
Community Support
Community Support

Hi @user12 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

yingyinr_0-1613633051959.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @yingyinr,

 

thanks for your amazing support. This is very close to what I would like it to look like. However, this is now only counting the SUM(In/Out) for the IDs that appear twice. What I need is a formula, that only counts negative In/Out values (-1) if their ID has already appeared with a positive value (1). Positive values (1) should be counted regardles of whether they appear only once.

 

I tried to clarify it in the attached screenshot

Screenshot 2021-02-18 092343.jpg

yingyinr
Community Support
Community Support

Hi @user12 ,

I updated the formula of measure as below(the part in red font is new added):

Measure =
CALCULATE (
SUM ( 'Book'[In/Out] ),
FILTER (
ALLSELECTED ( 'Book' ),
'Book'[Category] = SELECTEDVALUE ( 'Book'[Category] )
&& 'Book'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
&& ( [Count] > 1
|| ( [Count] = 1
&& 'Book'[In/Out] = 1 ) )
)
)
Measure = 
CALCULATE (
    SUM ( 'Book'[In/Out] ),
    FILTER (
        ALLSELECTED ( 'Book' ),
        'Book'[Category] = SELECTEDVALUE ( 'Book'[Category] )
            && 'Book'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
            && ( [Count] > 1
            || ( [Count] = 1
            && 'Book'[In/Out] = 1 ) )
    )
)

yingyinr_0-1613637892746.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HI @yingyinr ,

 

thank you so much for your support! Now it is working perfectly!!!!

 

I only have one last issue - in the real data I am considering data starting from 2010. In the graph however, I do not want to see the entire time range. I know that I can set the x-axis range in the Format section of the visual but is there a more userfriendly way for the users of my report to change the X-Axis interval?

 

I tried to solve this using a slicer but if I set a slicer to a time range between 1.1.2020 - 31.12.2020 the measure does not count the In/Out values prio to the 1.1.2020.

 

Not sure this can be solved by a slicer. Maybe drilldown can be activated somehow.

 

Thanks in advance and again thanks for your tremendous support so far!!

yingyinr
Community Support
Community Support

Hi @user12 ,

You may try to set a visual-level filtering for the graph. 

yingyinr_1-1614044428272.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @yingyinr ,

 

thanks for your answer. If I do this, the graph only shows data on or after the set date. Since my inventory is based on all data available leading up to this date however, the filter leads to incorrect results.

 

What is working so far is just setting the x axis to specific dates. But since my users cannot change the visuals layout, this is not a very user friendly solution.


Is there any way to allow users to change to X-Axis range? Ideally with some sort of slicer? Or is the X-Axis range onyl available to users with edditing rights?

 

Thanks!

yingyinr
Community Support
Community Support

Hi @user12 ,

You can create a date range slicer with X axis field Date just as below screenshot, then the user can adjust the date range by option even though they are report viewer...

yingyinr_0-1614240673309.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @yingyinr ,

 

unfortunately this is not really working for me. If I select the range 1.1.2020-31.12.2020 the displayed stock on the 1.1.2020 is 0. For some reason the part "

'Stock'[Datum] <= SELECTEDVALUE ( 'Date'[Date] )" is not working. The measure is not taking numbers before the 1.1.2020 into its calculation.
 
You seem to have the same issue in the example shared with me. The number on the 1.1.2021 should be 2 bikes + 1 car, since 1 bike and 1 car already entered the inventory in 2020.
 
What I need is a way to use a slicer or something like it that does not work as a filter on the data/measure but ONLY change the settings for the X-axis.
 
Is there any way to do this?
 
Also I am having some issues with the graph that you helped me create. I regularly get the error that "Visual has exceeded the available resources". Is there any way to improve this?
amitchandak
Super User
Super User

@user12 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

waterfall visual can be an option, but need check if that can fit in



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors