Dynamic year selection on an OLAP cube — INDEX() breaks for members with different year-spans
Setup: Tableau Cloud, web authoring (no Desktop), on a multidimensional OLAP cube. On the cube I can't reference the year in a calc — [Year] = [p.An] returns "undefined field" — so I select the year positionally with a table calc: INDEX() = [p.An] - 2022 (filter = TRUE, Compute Using = Year). YoY via LOOKUP([Measure], -1).
Problem: This is fine for aggregate views, but in a product-level table the partition is per product and INDEX() is positional. Products that don't exist in every year (new / discontinued items) get shifted indices, so the filter picks the wrong year for them. Example: with p.An = 2025, three brand-new (2026-only) products appear showing their 2026 figures.
Question: What's the recommended way to do a fully dynamic, parameter-driven year selection on an OLAP cube (web authoring) for member-level tables
, robust to members with different year-spans — given I can't compare [Year] to a parameter in a calc? Is densification/domain padding or a specific table-calc pattern the way to go, or is a model-side fix the only option?
#Tableau Cloud
Hi, @Draghici Ionut
It's been a while since this was posted, so you may have already found a solution. However, I wanted to share one possible approach.
If I understand correctly, the issue is that in a product-level table, products that do not exist in every year (for example, new or discontinued products) end up with different year positions. As a result, INDEX() points to different years depending on the product.
If that's the case, I think a model-side solution is probably the most practical approach.
- Expose the Year attribute from the cube so it can be referenced directly.
- If that's not possible, pad the data on the cube side so that every product has records for every year, even when the measure values are null.
- If neither option is feasible because of data volume or other constraints, continue using the INDEX() approach.
To me, INDEX() is essentially a workaround based on position rather than the actual year value, so it can be difficult to make it fully reliable when products have different year histories.
