Active sales promotions per week

[StefanBa]

Hello dear Excellers!

I'm posting here after hitting a wall with Excel for an awful amount of time, and despite my nature of not giving up easy, I had to post this thread.

The problem

I have an imported table which consists of information about sales promotions. What I'm currently looking into and most interested in is the number of weeks each promotion was valid (or active) and also which calendar weeks it is in.
What I'm working with

The way I use to calculate the duration of a sales promotion is fairly simple - I have the starting date and the finish date of the promotion in each row. (1 row = 1 promotion)

The goal

However, what makes things a bit more spicy and complicated is the fact that I want to be able to do a Pivot Chart (Clustered column or a Line chart) with every week of the year on the horizontal axis and the count of active promotions for this specific weeks, such as:

I tried with creating some measures, as I guess the solution is most likely hidden somewhere in there, but as you can already guess, I had no success at all. My next attempt was trying to add multiple columns which list every week a promotion was active (some sales promotions last for over a year, so I added 57 columns as a safe margin) and then planned on including them as columns in a pivot chart, but I quickly discovered that the Pivot Chart has a limit of 255 line items (it stops adding columns after the 9th).

I'll be super grateful if someone can give me an idea.

Thank you for your time, guys!

 

[StefanBa]

Hi again,
The complete dataset is fairly obfuscated with a lot of additional and unnecessary data, but here I tried to clean it and make it a bit more relevant for this specific purpose.

I thought about doing a CountIFS(), but the way I see this one working is by making an additional table, where all the individual week numbers will be listed in the first column, and the
total count of active promotions per this week will be listed in the second column. This would work perfectly fine for generating the graph to some extent (I'm still not entirely sure how can I deal with the issue of a promotion expanding into the next year - then the weeknum of "Promotion End Date" will be lower than the weeknum of "Promotion Start date" and I also would like to filter by year and etc), but the thing that concerns me about that approach is that I won't be able to list the individual promotions by clicking on specific week on the pivot table.

I'd also like to apply some filtering, based on other parameters, such as "Company Code Description", "Creator Name" and etc.


I would appreciate any sense of direction.

​