Filtering Inventory Dashboard to a Specific Date Using dbt Snapshot (valid_from/valid_to)

[Laflet]

Hi Sisense Community,

I'm working on an inventory dashboard in Sisense and I'm using dbt Snapshots to maintain historical inventory data. My snapshot table includes dbt_valid_from and dbt_valid_to columns to track the period for which each inventory record was valid.

I want to enable users to filter the dashboard to a specific date and see the inventory position on that exact day. For example, if a user selects "12/04/2025" (April 12, 2025), the dashboard should display the inventory levels as they were on that date, based on the dbt_valid_from and dbt_valid_to ranges.

My inventory_history table in the ElastiCube has the following relevant columns:

• product_id
• location_id
• quantity_on_hand
• dbt_valid_from (Date/Timestamp)
• dbt_valid_to (Date/Timestamp, can be NULL for current records)

I'm looking for the best way to implement a filter that allows users to select a single "Inventory Position Date" and then filter my inventory widgets to show only the records where the selected date falls within the dbt_valid_from and dbt_valid_to range (or dbt_valid_from is before the selected date and dbt_valid_to is NULL).

Specifically, I'm wondering:

1. What is the best approach to allow users to select a single date for this filtering? 

2. How would I create the necessary logic (likely a Calculated Field) in the ElastiCube to determine if an inventory record was valid on the selected date? I need to compare the selected date with the dbt_valid_from and dbt_valid_to columns, handling the NULL value in dbt_valid_to for current records.

3. How would I then use this calculated field (or another method) to filter my inventory widgets to show the correct data for the selected date?

Any guidance, examples of calculated field formulas, or best practices for implementing this type of point-in-time filtering in Sisense would be greatly appreciated!

Thanks in advance for your help.

[Ido_QBeeQ]

Hey @Laflet interesting use case, a classic inventory challenge,

I hope I can shed some light here, LMK if I can be clearer.

Per your questions;

1. I am sure there are a few ways to achieve this, the option below is definitely the more straightforward and quick way.

2. My approach involves counting the days between valid_from to valid_to, and then comparing this value in the dashboard date filter. To achieve this I would use a combo of a Day Diff custom column in the Elasticube (as you rightly assume)

daydiff([dbt_valid_to (Date/Timestamp, can be NULL for current records)],[dbt_valid_from (Date/Timestamp)])

This will actually give you the number of days the item was on inventory, starting from the valid_from date.

3. and then combine the custom column from step 2 above with the Filtered Measure plugin available on the Sisense Marketplace (free)

In this step, we calculate the Day Difference again, using the dashboard formula, but this time we compare the valid_from date in the pivot or formula, to the actual date filter on the dashboard filter pane. We will then compare this Day Diff to the day diff calculation from the custom column in the EC and filter out irrelevant records.

Please find another detailed article about this plugin here by AssafHanina @AssafHanina , strongly recommended to review as it covers a few use cases which can help in this or at least inspire on using other approaches.

The formula should look like this, although it might need some modification (notice the '@' sign in the formula, in the second date, this is to indicate to the Filtered Measure plugin to reference that date filter:

min(DDiff([Days in dbt_valid_from (Date/Timestamp)],[@Days in dbt_valid_from (Date/Timestamp)]))

This can be achieved by creating a flag in a new formula which defines that if the Day Diff from the EC is larger or smaller (depending on your logic) then create a flag 1, then filter out those 1 values. This is also the place to include the logic for the NULL issue you mentioned above.

I hope this helps, it gets a bit complicated when trying to convey complicated things over a community comment hehe 🙂

Another way to achieve this would be by use of our powerful Advanced Dashboard Filters or Input Parameters plugin, though if it is for this specific dashboard only it may be a bit of an overkill, LMK if you'd like to try it out.

If you'd like to meet up and review this or anything else, please don't hesitate to reach out, we're always here to help,

We're always here to help 😊

Ido from QBeeQ

QBeeQ - Gold Sisense Partner

info@qbeeq.io

Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news!

[DRay]

Hi @Laflet,

Thank you for reaching out. 

I’m following up to see if the solution offered by @Ido_QBeeQ worked for you.

If so, please click the 'Accept as Solution' button on their post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.