I think you are taking right approach and as long as I know, it is the best way.
Tableau cannot "Create" data, then it requires master data of Month (//or other time grain) x Customer Name.
You could use UNION instead of a CROSS JOIN as shown by Joe Mako in Re: Headcount, when given the Arrival & Departure Dates.
The benefit of a UNION is that it "only" doubles the number of rows.
The cost is described in #3 and #4 in Re: Headcount, when given the Arrival & Departure Dates.
You might like to up-vote Date period as multiple marks and thus encourage Tableau to invent a better solution for date duration vs calendar periods.
I believe this scenario is why Multi-Values Parameters has not been implemented.
Theoretically, one could instantiate a Multi-Valued Parameter via a Calculated Field. And then use another Calculated field to only represent dates between Start and End Dates.
This would essentially create a Cross-Joined query (Which could blow things up depending on the number of rows, and cardinalality).
Until this is figured out, we have what we have.
Ideally, if you can Cross Join the data on the backend, do it. It makes working with Tableau a lot easier.
If you can't, then the methods Kettan pointed to should help you out.
I'll also add this in, as it is very relative to the situation, and provides an alternative method.
I think I cracked this one although the solution is rather cumbersome.
See attached 9.2 workbook.
The trick I used is to split the available records into 2 sets: one to calculate the overall minimum date and one for overall max. This is done by introducing another date field called [Unified Date]:
IF [Invoice End Month] <> [LoD max date] THEN [LoD min date] ELSE [LoD max date] END
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:
Then I introduced the following calcs:
RUNNING_MIN(min([Invoice Start Month]))
DATEADD('month', index()-1, [min date])
if (LOOKUP(MIN([Invoice Start Month]),-index()+1)<=[The Date] OR LOOKUP(MIN([Invoice Start Month]),-index()+size())<=[The Date] ) AND (LOOKUP(MAX([Invoice End Month]), -index()+1)>=[The Date] OR LOOKUP(MAX([Invoice End Month]), size()-INDEX())>=[The Date]) THEN WINDOW_MAX(MIN([Monthly Invoice Amount])) else 0 end
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.
This particular set of pills did the trick:
using the following partitioning&addressing: