Same Store Flag (Sales in all Weeks of Current Period & Same Period Year Ago)

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?

have you considered filtering the slicer by a measure?

As you can see in this screenshot, I have added a filter to the slicer (see filter pane), which is not visible on the slicer itself, but it DOES impact the slicer - showing only the IDX values that do not have a total equal to zero. (In this example, IDX 2 does not show - the table is included in the screenshot so you can see that IDX 2 does have a zero total)


Interesting idea, thanks … I’m under the impression that I need the calculated column because I need Power BI to go row by row, counting the number of weeks of each store, and excluding any stores that are missing weeks. Otherwise, if I count the number of weeks with a measure, it will be applied to the aggregate of all stores in the selection and return with no missing weeks.

Hi @Eric, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!