How to calculate the daily warehouse stock in DAX?

0
votes

I have a table in SSAS tabular mode that shows how individual pieces of products moved through different sections of a production line:

Product_ID, section_ID, Category_id (product category), time_in (when a product entered the section), time_out (when the product exited the section)

This is how the input table looks like:

enter image description here

I would like to write a measure in DAX that can show me the stock of each section and product category day-by-day as shown below by counting the number of distinct product ids which were in a particular section on that day.

enter image description here

I'm using SQL Server 2017 Analysis Services in Tabular Mode and Excel Pivot Table for representation.

1
excelssasdaxssas-tabular
So your image is your desired output? Can you show what the input looks like as well? – Alexis Olson
@AlexisOlson I have added the input. – Andris

1 Answers

1
votes

Create a new table that has all of the dates that you want to use for your columns. Here's one possibility:

Dates = CALENDAR(MIN(ProductInOut[time_in]), MAX(ProductInOut[time_out]))

Now create a measure that counts rows in your input table satisfying a condition.

ProductCount = 
    VAR DateColumn = MAX(Dates[Date])
    RETURN COUNTROWS(FILTER(ProductInOut,
               ProductInOut[time_in] <= DateColumn &&
               ProductInOut[time_out] >= DateColumn)) + 0

Now you should be able to set up a pivot table with Category_id on the rows and Dates[Date] on the columns and ProductCount as the values.

Output Matrix