Tableau - Running Total for Previous 24 Months

Question

How can I make something like this? Note that ordering of Month was dependent in Parameter(Display Date). Parameter(Display Date) presents the last month to display.

Those line represent the following:

Will this be possible for tableau?

Answer 1

I'm going to assume that your date field is called Date and that the value you're measuring is called Value.

You just need two calculated fields:

Last Year / Current Year:

IF DATETRUNC('month', [Display Date]) >= DATETRUNC('month', [Date])
AND DATEADD('month', -11, DATETRUNC('month', [Display Date])) <= DATETRUNC('month', [Date])
THEN 'Current Year'
ELSEIF DATEADD('year', -1, DATETRUNC('month', [Display Date])) >= DATETRUNC('month', [Date])
AND DATEADD('month', -23, DATETRUNC('month', [Display Date])) <= DATETRUNC('month', [Date])
THEN 'Last Year'
END

There's a solid chance that it's possible to shorten that, but it gets the job done. The DATETRUNCs are to make sure that dates from the entire month are considered. Note that if a date is not in the current year or last year, then this field will be null.

Toss MONTH(Date) in the Columns shelf (make sure it's discrete), and toss SUM(Value) in the Rows shelf. Add Last Year / Current Year to the Color shelf to split the line into two, and then filter out the nulls for your Last Year / Current Year field.

At this point, everything should be working except that your chart starts with January (or the beginning of your fiscal year). We'll need to make a sort field.

Month Sort Field:

(MONTH([Date]) + (11 - MONTH([Display Date]))) % 12

This will assign a number from 1 - 12 to each date, with 12 being the month of Display Date and 1 being the month after Display Date.

Now right click on the MONTH(Date) in the Columns shelf and hit Sort.... Go to Field, choose Month Sort Field, and your months will sort such that Display Date is the final month on the axis.