如何用DAX计算仓库每日库存及SSAS表格模型中生产区域分品类日库存？

Alright, let's break down how to create the DAX measure you need to track daily inventory counts by production section and product category. The core logic is to count unique products that were present in a section on a specific date—meaning they entered on or before the date, and either haven't left yet or left after the date.

Step 1: Basic DAX Measure #

First, assuming you have a dedicated Date dimension table (critical for time-based analysis in SSAS), here's the foundational measure:

Daily Inventory = 
VAR CurrentReportDate = MAX('Date'[Date]) -- Grabs the active date from your report's filter context
RETURN
CALCULATE(
    DISTINCTCOUNT('Product Flow'[Product_ID]), -- Count unique products per section/category
    -- Filter products that entered on or before the target date
    'Product Flow'[time_in] <= CurrentReportDate,
    -- Filter products that either haven't left (NULL time_out) or left after the target date
    OR(ISBLANK('Product Flow'[time_out]), 'Product Flow'[time_out] > CurrentReportDate)
)

How This Works: #

• VAR CurrentReportDate: Pulls the date from your report's context (e.g., a date slicer, row/column date grouping).
• DISTINCTCOUNT('Product Flow'[Product_ID]): Ensures we count each product only once per section/category, even if your table has edge cases with duplicate entries.
• The paired filter conditions isolate exactly the products that were in the section on the target date.

Step 2: Key Setup Notes #

• Date Dimension Table: You must have a properly structured Date table (continuous dates, no gaps) linked to your Product Flow table. This table drives the daily context for your inventory calculations.
• Handling NULL time_out: The ISBLANK check accounts for products that are still in the section (haven't been recorded as leaving yet).
• Automatic Context Preservation: The measure respects any filters you apply to section_ID or Category_id—just drop those fields into your report's rows/columns, and the measure will aggregate accordingly.

Step 3: Optimized Version (For Large Datasets) #

If your Product Flow table is large, this version uses more efficient filter logic to boost performance by avoiding explicit full-table scans:

Daily Inventory Optimized = 
VAR CurrentReportDate = MAX('Date'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT('Product Flow'[Product_ID]),
    KEEPFILTERS('Product Flow'[time_in] <= CurrentReportDate),
    KEEPFILTERS(OR(ISBLANK('Product Flow'[time_out]), 'Product Flow'[time_out] > CurrentReportDate))
)

KEEPFILTERS ensures that any existing filters on your Product Flow table (like specific categories or sections) are retained alongside our date-based filters, preventing unexpected overwrites.

How to Use in Your Report #

1. Add your Date table's Date field to the rows/columns of your visual.
2. Drag section_ID and Category_id into the visual's axis or legend area.
3. Drop the Daily Inventory measure into the values section.

You'll now see a clear daily breakdown of unique products in each section, grouped by product category.

内容的提问来源于stack exchange，提问作者Andris