hi Caleb,

So the key to this is to create a common baseline for each year. I've broken this down into stages, but you can nest this all in one (or 2) calculations if you wish.

First we need to get the First Date for each year. We can do this with an LoD expression

[First Date of Each Years Promos]

{FIXED [Year]: MIN([Datetime])}

Then we can use this to work out how many days from this start each day is...

[Promo Days]

DATEDIFF('day',[First Date of Each Years Promos],[Datetime])

We can now use this as our common baseline (so for all years Promo Days = 0 is the first day, and so on.

Finally we can plot this on a line chart, and use the "quick table calculation" option to get a running sum

Hope that is what you were after and all makes sense.

btw if you want to filter out certain dates, just filter them out and everything should update to reflect what is unfiltered (unless you are removing dates which would be the start, in that case you'd need to also add the date filter to be "in context" so it affects out first LoD, which gets the first date)

Good morning

see the attached

the start and end dates are hard coded

then used in filters set to true

and the promo days are  - convert to discrete dimension

and make the viz

Jim

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.

oops my bad! I accidentally did it on the 2019 Beta!

Here you go...in an actually released version!!

hi Vanessa,

Glad it helped. With regards your second question, if you change the LoD expression from a MIN to a MAX it will now ensure that the promos end on the same date, rather than start on the same date (I've renamed the field from First Date... to Last Date...

[Last Date of Each Years Promos]

{FIXED [Year]: MAX([Datetime])}

There are other ways you can specify the running sum to work in reverse, but will this does the trick, so no need to complicate things!

On your first question ...In Tableau (like many things) there are lots of ways to achieve the same thing, and which is best depends on the situation (and sometimes no one way is "better"). I guess mine has the advantage of being more dynamic, in that the last promo date is picked up in the formula, but as you have to hard-type the Promo End Date anyway,  Jim's will be more efficient as mine has to perform an LoD calculation, which has some computational cost. You won't really tell the difference unless you get into the 10s and 100s of millions of rows of data.

I've attached an updated version and put a formula in to filter out the non-needed dates (which I did have to make a "context filter" so it affects the {FIXED [Year]: MAX([Datetime])} formula

Ah those pesky bosses and their wants!!

Cool. Yes that's the way I'd suggest. We can specify the Running Sum to run in the other direction, but why work harder!

In fact you could go with a custom formatting of

"";#,##0;#,##0

which would remove the positive numbers