Warehouse stock balancing: Data modelling/DAX measure advice

[Anonymous]

Hello, I am hoping someone can help advise the best way to model a warehouse type data set / create a measure..

I am showing warehouse stock levels using a matrix table, each cell represents shelf location availability (link to sample pbix file below). Each cell in the matrix can store 2 items as a maximum. As inventory changes over time (I have added a calendar table) I'd like the hover over info to show what the current storage location has in it *without seeing the history of past items that were stored there*.  

Sample pbix file here.. 

The ingoing and outbound movements are in the same table at the moment, should they be separate tables?

Any advice would be appreciated! Thanks in advance 🙂

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

For hiding UPN went in and out again,

Create a measure like so:

Measure = 
IF (
    CALCULATE ( [(Temp) In-out], ALLEXCEPT ( Table1, Table1[URN] ) ) = 0
        || CALCULATE ( [(Temp) In-out], ALLEXCEPT ( Table1, Table1[URN] ) ) = BLANK (),
    0,
    1
)

And then put it on "Filters on this visual" of the table on your Tooltip page.

For removing the 0's,

Change your [(Temp) In-out] measure like so:

(Temp) In-out = 
VAR In_Out = [Inbound] - [Outgoing]
RETURN
    IF ( In_Out = 0, BLANK (), In_Out )

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Anonymous]

Thats a really helpful explanation and works perfectly - @Icey thank you so much! 😊