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.
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