topic Re: Track Stock amount in DAX Commands and Tips

Track Stock amount

JoRose — Fri, 22 Dec 2023 13:55:28 GMT

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

Re: Track Stock amount

some_bih — Fri, 22 Dec 2023 13:08:14 GMT

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)

Re: Track Stock amount

JoRose — Fri, 22 Dec 2023 14:01:33 GMT

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

Re: Track Stock amount

some_bih — Fri, 22 Dec 2023 14:20:16 GMT

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

Re: Track Stock amount

JoRose — Wed, 24 Jan 2024 09:28:42 GMT

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.

Re: Track Stock amount

some_bih — Thu, 11 Jan 2024 14:19:59 GMT

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.