Search code examples
excelssasdaxssas-tabular

How to calculate the daily warehouse stock in DAX?

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.

Solution

  • 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