Help with 5 yr historical data

[hemakrishnamoor]

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 year of the date column that I need to show in the report), I need to show data from the year chosen going back 5 years. aas the filter and data are coming from the same field, no matter how much I try in DAX to removefilter and retriecing 5 years data and adding new filter, it either shows all years and not stopping at -5 or shows only the year selected. Is there a way to do this?

[v-moharafi-msft]

Hi  @hemakrishnamoor  ,

We wanted to check if your question has been resolved or if you are still facing any confusion feel free to reach out. Providing an update can be beneficial for others who might be experiencing similar challenges.

Best Regards,

Abdul Rafi

[hemakrishnamoor]

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.

[v-moharafi-msft]

Hi  @hemakrishnamoor ,

Thank you for reaching out to Microsoft Fabric Community and Thanks to @lbendlin   , @danextian  and @cengizhanarslan   for Sharing valuable insights.

Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

 Best Regards,

Abdul Rafi.

[cengizhanarslan]

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]

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]

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

[danextian]

Hi @hemakrishnamoor 

Are you able to connect to the semantic model via direct query. As other users have already suggested, the only solution is to use a disconnected table which cannot be done with live  connection as this requires modifying the model. A filter from a related or the same table will show only what's been selected.

[krishnakanth240]

Hi @hemakrishnamoor 

Since you can not modify the model, workaround is to use a disconnected table for Year

Year Slicer = DISTINCT('Table'[Year])

Then

5Y Data =

VAR SelectedYear = SELECTEDVALUE('Year Slicer'[Year])

RETURN

CALCULATE([Your Measure],

FILTER(ALL('Table'[Year]),

'Table'[Year] <= SelectedYear &&

'Table'[Year] > SelectedYear - 5))

Slicer comes from disconnected table. Measure applies custom filter logic for last 5 years. Avoids conflict with existing filter context

[hemakrishnamoor]

This does not work for me either. If possible, please send me a sample workbook showing this. 

[lbendlin]

Your filter needs to be fed from a disconnected table, cannot be fed directly from the calendar dimension table.

[hemakrishnamoor]

I just have the one table that has the data in it and the column record create date, which has a hierarchy. The year of that is what the user picks  for the report. The matrix below shows yr and yr -5 from the same table. Is that possible?

[lbendlin]

The year of that is what the user picks  for the report. The matrix below shows yr and yr -5 from the same table. Is that possible?

No.  You need a disconnected table. Your semantic model should also have a calendar table.