Forum Discussion
Help with 5 yr historical data
Hi,
I have solved it in a different way as my hands were tied with a strict semantic model with no ways of creating a date table or adding a column. Instead of pulling all the 5 years worth historical data together, I had individual measures going after year -1, year -2, etc and solved it. Thank you all for the reply and suggestions. Truly appreciate it.
If I create the YearSelector, I cannot use it in a slicer because it is a measure, correct? How do I work around that?
Hi hemakrishnamoor ,
Thank you for your patience.
I recreated the same scenario using a slicer and matrix with the same Year field to better understand the behavior.
What you’re experiencing is expected due to how filter context works in Power BI. When the slicer and the visual both use the same Year field, the slicer removes all other years before the measure is evaluated, so DAX (even with REMOVEFILTERS) cannot bring those rows back.
The standard approach for this requirement is to use a disconnected Year table so the slicer logic is separated from the reporting data. I tested this approach in a sample model, and the rolling 5-year logic worked correctly once the slicer was disconnected from the main table. I’m sharing screenshots from the recreated setup for reference.
Also, YearSelector should be created as a calculated table, not a measure. Once created as a table, its Year column can be used directly in the slicer.
However, since you’re working with a published semantic model and don’t have permission to modify the model, implementing this directly in the current report may not be possible.
Few Workarounds possible in this scenario :
- Requesting the dataset owner to add a proper Date table or disconnected Year table in the semantic model,
- Enabling a local/composite model using “Make changes to this model” (if permitted in your environment)
For details, see the documentation:
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
Best Regards,
Abdul Rafi