Track inventory level over time

Hi Anonymous ,

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 ) )
    )
)

Best Regards

Hi Anonymous ,

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

Best Regards

Hi Anonymous,

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 Anonymous ,

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 Anonymous ,

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

Best Regards

Hi Anonymous ,

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 Anonymous ,

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 Anonymous ,

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 "