Forum Discussion

hemakrishnamoor's avatar
hemakrishnamoor
Frequent Visitor
4 months ago
Solved

Help with 5 yr historical data

I have a situation where I am using a published semantic model and so no rights to create a table or add another column. When the user selects a year (there is no date table in this model. Just the y...
Need Help
  • hemakrishnamoor's avatar
    hemakrishnamoor
    3 months ago

    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.

cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
4 months ago

Step 1) Create a disconnected year slicer table

YearSelector =
DISTINCT (
    SELECTCOLUMNS (
        ALLNOBLANKROW ( FactTable[YearColumn] ),
        "Selected Year", FactTable[YearColumn]
    )
)

 

Step 2) Use this as your year slicer instead of the original year column

Remove the original year from the slicer and use YearSelector[Selected Year] instead.

 

Step 3) Create a measure that filters the data to the 5-year window

Value Last 5 Years =
VAR _SelectedYear =
    SELECTEDVALUE ( YearSelector[Selected Year] )
VAR _StartYear = _SelectedYear - 4
RETURN
    CALCULATE (
        [Your Base Measure],
        REMOVEFILTERS ( FactTable[YearColumn] ),
        FactTable[YearColumn] >= _StartYear,
        FactTable[YearColumn] <= _SelectedYear
    )

 

Step 4) Control which years appear as rows in your visual

Add a visual-level filter using this helper measure:

Is In 5 Year Window =
VAR _SelectedYear =
    SELECTEDVALUE ( YearSelector[Selected Year] )
RETURN
    IF (
        MAX ( FactTable[YearColumn] ) >= _SelectedYear - 4
            && MAX ( FactTable[YearColumn] ) <= _SelectedYear,
        1,
        BLANK ()
    )

Add Is In 5 Year Window to Filters on this visual → set to is 1.

  • hemakrishnamoor's avatar
    hemakrishnamoor
    Frequent Visitor
    4 months ago

    If I create the YearSelector, I cannot use it in a slicer because it is a measure, correct? How do I work around that?

     

     
    • v-moharafi-msft's avatar
      v-moharafi-msft
      Icon for Community Support rankCommunity Support
      4 months ago

      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