Filtered Measures Using Snapshots and Sisense Date Calculations

npatel_02 - how many inventory records are involved, and how many stock dates are relevant to the use case?  The reason I ask is because a potential modeling solution involves pre-calculating the stock status for each product/date combination.  It makes things quite straight-forward, but there are potential limitations depending on data volume / disk size.

To limit the size as much as possible, the example would employ 3 tables:

• Date Dimension: unique list of dates, for dashboard filtering purposes
• Inventory: collection of products in inventory, along with dbt_valid_from and dbt_valid_to (like your attached sample data)
• Inventory Status: Cartesian product of date & inventory UID.  This table contains Date, Inventory UID, and a calculated IsActive flag, which joins to the inventory table to check if the Date falls between dbt_valid_to and dbt_valid_from, producing a 1 or 0 for filtering purposes.

Inventory Status would then serve as the key between your date dimension and inventory tables.

Considerations:

• Size of Inventory Status table
• This is a supervised many-to-many, so you'd need to restrict the Date dashboard filter to single-select (not multi-select or range or anything like that).
• Ensure the widget(s) or dashboard are filtered to IsActive = 1 to return the desired inventory records

If this approach isn't feasible, I do believe we could come up with something a bit more dynamic with a combination of scripting and/or BloX actions.  But this may require a more formal engagement with our Field Engineering Services team.