see the attached
thenis the formula I used
this is not the same data that produced your image so it is a little difficult to check
but it returns this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
I apologize, some of the data I meant to include didn't go through. I have attached an updated workbook and brought in your suggested formulas. It seems that the stores with partial year data are being included in the "Comp Store" filter?
One idea I had was to use Countd to determine how many weeks of sales each store has and compare that to the maximum (based on 2017-2018 sales) to figure out which stores qualify as a Comp store. The main problem I was running into was I couldn't use the Max and Countd formulas at the same time since they're both aggregates.
Do you have any other suggestions or maybe an edit to your previous formulas?
Thank you so much for your help!
Please Check The Screenshots and attached. This is Some Out of the Box Type Solution Please Have a look. So These are my Calcs
This is just Showing your actual data and replaces Nulls with 0
Next Step Converts your actual to 1 and keep 0 also
I could have done it in a single step too, but wanted to Show Your actuals
The Final Calc Goes across to Check Dynamically if your SUM across Table is >=75, Then it is Comp otherwise Non Comp, so You will get only Complete Rows.
If it Helps, Pl mark it Helpful and CORRECT to Close Thread