Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You could make three DAX columns on your date table. One for Date = Today, one for Year and Month Today = Year and Month of Date, and Year of Today = Year of Date. If needed, you could also use ISINSCOPE on your Date and Month columns to dynamically return one of 3 similar measures (using those three Date table columns).
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |