Prior year dimension to show based on the year

Hi All

My company has a listing of stores which are considered to be valid for comparison on a year vs prior year basis . Let’s call this comp

The reason a store would be invalid as comp could be anything from having a major renovation in the year or prior year , being new during the year, or being closing due to adverse business results etc. In short any of the detailed instances would result in misleading analysis because the change creates a significant variance in business results. To factor into my analysis in trying to build comp vs non comp for multiple years

I have built the comp field in my fact table on what was considered valid as Y and not valid as N . The problem I’m coming across is when I try to leverage this field as a filter on a simple prior year calculate the current date value isn’t considered but rather what was prior year.

For example

Store A has no change this year making it valid or Y but had a change two years ago making it invalid comp or a value of N. When I pull current year comp values store A shows sales this year but not sales last year

Any suggestions would be appreciated

Would it be possible for you to load some sample data and maybe a quick mock-up of what you would like to see? Generally get faster and better answers that way