I have a seemingly simple problem that has stumped me and many Tableau "Doctors", and I'm hoping you can help me out. I have a data set with a 'start month', an 'end month', and a 'monthly value', and I'm looking for a way to plot monthly value over time. Here is a simplified version of the data set I'm using:

Customer Name

Monthly Invoice Amount

Invoice Start Month

Invoice End Month

Company A

$200

January 2015

July 2015

I've tried all sorts of calculations and techniques to allow me to plot 'total invoice amount' by month, but the only way I have been able to accomplish this successfully is by cross joining my data set with a table containing all months present in my data set, so that I have a row for each company and month. So, for "Company A" in my data above, the new data set resulting from the cross join would look like this (7 rows instead of 1).

Customer Name

Month

Monthly Invoice Amount

Company A

January 2015

$200

Company A

February 2015

$200

Company A

March 2015

$200

Company A

April 2015

$200

Company A

May 2015

$200

Company A

June 2015

$200

Company A

July 2015

$200

While the cross joining method does work, it does not scale well, especially when you have thousands of customers spanning many years. Plus, there are times when we want to look at daily, or weekly amounts, which would make the resulting cross-joined data set even larger.

Again, I'm looking for a calculation or technique that will allow me to plot value over time (in this case invoice amount) when I have "Start" and "End" dates in my data set, without having to cross join to another table. I have attached a packaged workbook with sample data. We're still using version 8.3, but if there is a solution in Tableau 9, that would be great too.

Here is why I said it was cumbersome: the above formula might not yield the expected results, i.e. overall min&max. It may be the case with very scarce dataset where one record holds both min&max. Anyways it works with your 5 rows...

Having at least 2 records ranging in dates we can fill in the gaps using "Show Missing Values" option. Here on the level of Months:

Even though we are showing missing months any standard dimension or measure is NULL in these "missing" months in between since every record belongs to either our min or max unified date. But with a help of table calculations we can lookup the right thing.