I want to have a slicer that I can use to filter to only stores that have sales in all weeks of the current period and the same period a year ago. My sales are reported by week in the fact table, so I’ve built a custom date table by week and I have a separate store list table as well.
I figure the best solution for a same store filter is to create a measure to count all weeks with sales greater than zero in the current period as well as the same period year ago, then create a calculated column in my store list table with the logic IF # OF WEEKS SELLING = MAXIMUM WEEKS, “Same Stores Only”,“Balance of Stores”, then I can use that column as my slicer. However, I’m open to other ideas if there is a better way because I can’t get this to work.
I created the week count w/ sales >0 measure as follows:
calculate(DISTINCTCOUNT(‘Date Table’[Week Ending]),
filter(‘Date Table’,[Sales Current Period]>0 && [Sales YAGO]>0))
However, when I try to create a calculated column in the store list with an If/THEN statement, it does not work properly and it takes a very long time to process. Perhaps the issue I’m running into is that the user can select any custom time frame, so the maximum number of weeks is not constant and a calculated column can not be dynamic? I’m OK with only looking at the last 13 weeks versus the same 13 weeks one year ago if that solves the problem, but I’ve run into issues when trying to do that.
Any ideas?
