Hi,
I have a small dimension in my tabular model with only 3 values. Past, Current and Future.
The dimension is used with a snapshot fact table that contains 3 snapshots per month. On the 1st, the current day and last day of the month.
The dimension specifies whether the date is in the past, is current or is in the future. Snapshots are calculated 5 years in advance for forecasting reasons.
This works fine but the issue arises when using a date hierarchy (Year - Month - Day)
When rolling up to Januari 2021 , the dimension value returns "Future" probably because the last snapshot of January is still marked as Future. I need it to show Current because Januari 2021 is the current month.
Also on a Year level, 2021 is shown as Future for the same reasons. It should show current as well.
We're migrating from a multidimensional model and we used to solve this with dynamic sets but that's not available in tabular models.
Any ideas on how to solve this? I don't quite see a solution because there is no such thing as "dynamic" dimension columns.
We can't use a measure because they need the field as a filter in excel as well.
