Track Stock amount

[JoRose]

Dear DAX/Power BI Community,

I have the following issue. I want to record warehouse stock on a daily basis. To restrict the amount of new entries in the database, i want to record only those products which quantity changed since the last update. The point i am struggeling with is: when i have a product which didnt change in quantity over a few days, it still needs to be counted as in stock with the quantityamount from the last date with a record for the given product.

The data in the Database could look like this:

As you can see the ProductID 220 has no entry for the 05.12 which means that its quantity is still 1.

The DAX formula should respect this and evaluate to the following result:

The underlying tabular datamodel looks like this:

So what i need is a measure that can deliver the result shown in the diagram. 

Any ideas are highly appreciated. I am really stuck right now.

Many thanks for your help and a Merry Christmas to you all!!!

[some_bih · Accepted solution]

Hi @JoRose I created two measure, based on your sample data, as following

Adjust your proper fact / Calendar tables names and you should get output from Output (Date from Date table, not fact)

1. Simple simple = SUM(factInventory[Quantity])

2. Sum adjusted =

IF (

    ISEMPTY ( factInventory ),

    CALCULATE ( [Simple simple] ,OFFSET ( -1,, ORDERBY ( 'Date'[Date] ), ) ),

    [Simple simple]

)

Output

[JoRose]

Track Stock amount

Helo @some_bih, thank you so much for your effort. In my simple example scenario it works perfectly. 

Anyway i still have issues with your solution. It only works when the offset between changes in stock amount are no more then 1 day. To show what i mean, i have slightly adjusted the sample data.

Fact table:

Example visual:

As you can see, there doesnt seem to be any stock for 220 at the 08. of december, but there are actually still 3 pieces 06. december. 

And finally one more point: I couldnt find a way to sum up the result of the measure 'sum adjusted'.

But in the end i need a visual which only shows one bar per day, whichs means ill have to sum up over all products on that day.

I hope you can help me out on that. If there are remaining questions reach out.

with kind regards.

[some_bih]

Hi @JoRose thank you for reaching. 

I am busy these days, but I will take a look and let you know. I hope you understand.

[some_bih]

Hi @JoRose what is your request?

You wrote " want to record only those products which quantity changed since the last update." so you want to flag something like calculate / include / exclude... provide expected output in table view (later you can do visuals)

[JoRose]

Hey @some_bih, i have updated my post. What i need is a measure that can calculate the result the shown in the diargam.