The difficulty of displaying FAQ: Open & Close Dates over time is experienced by many.
My hope is that the technique shared below will make it easier to do this for more people.
Whether one should split the period into two (union), days, months, or even years, depends much on the length of the periods and number of records.
|Years||Insurance etc.||Big data and long periods|
|Two (Union)||Big data and long periods||See also|
|Months||Insurance etc.||From a few months to a year|
if number of records isn't too extreme
|Days||Hotels, etc.||Short periods of a few days|
We can pre-calculate the cost of each method with a normal connection. The below is from the attached workbook:
The example below is based on the "Insurance" data.
Include a reference table (Period) with running numbers from 0 to the maximum number of calendar periods needed or more. The attached has 120 rows in the Sheet Period.
Cross join the data table (Insurance) with the "calendar period" table (Period) with a calculated field equal 1 on both sides:
As the last step before building our view, add a filter that acts as a "JOIN" condition:
Now we are ready to build our views!
DATEDIFF( 'day', [Cover Start Date], [Cover End Date] ) + 1
|Days In Period||:|
MAX( [Cover Start Date], [Period Month Start] ),
MIN( [Cover End Date], [Period Month End] )
) + 1
|Premium In Period||:||[Days In Period] / [Cover Days] * [Premium]|
Attached Workbook Version: 10.3