Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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
Hi @user12 ,
I have created a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
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!
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
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
I am somehow not unabled to upload pbix files
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
Hi @user12 ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
Best Regards
Hi @v-yiruan-msft,
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
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 ) )
)
)
Best Regards
HI @v-yiruan-msft ,
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!!
Hi @user12 ,
You may try to set a visual-level filtering for the graph.
Best Regards
Hi @v-yiruan-msft ,
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!
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...
Best Regards
Hi @v-yiruan-msft ,
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 "
@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