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.

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

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's avatar
    hemakrishnamoor
    Frequent Visitor
    4 months ago

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