I made an attempt, I think it may be doable with a few calculated fields:
1. Beginning of Window:
2. Plotted Start Date in Window:
IF [Start Date]<=[Beginning of Window] //begins before the window beginning
AND [End Date]>=[Beginning of Window] //ends after the window beginning
THEN [Beginning of Window] // then use the window beginning, not true start date
ELSEIF [Start Date]>=[Beginning of Window] //begins after the window beginning
AND [Start Date]<=TODAY() //and before today
THEN [Start Date] //just return the start date
3. Plotted End Date
IF [End Date]<=[Today] // don't allow hang over Today
THEN [End Date]
4. Plotted duration
DATEDIFF('day',[Plotted Started Date in Window],[Plotted End Date])
Please see workbook 2019.3 attached in the Forum Thread:
321957window.twbx 42.0 KB
This looks great, thanks Swaroop!
Just a quick note: I used the 2 year window as an example. I want to be able to filter results without any limitation. Preferably using the relative date filter option so the user can see results for previous year, past 3 years, or even past month, etc.
Hmm. That sounds tricky.
There generally isn't a way to know what a user has selected in a filter,
particularly one as free entry as that.
A whole other approach would be to set up a date scaffold that contains
every date you would be interested in.
Then your data set would be joined to the scaffold, and it would keep
every scaffold date between the start and the end.
Then your free form date filter would be created from the scaffold date and it
would retain just the dates in the window.
The drawback is that it might considerably enlarge your dataset.
How many rows are in your true data set?
If you think the resulting joined dataset would be manageable in size,
we can explore that option.
Here are some links on the subject of date scaffolds:
My actual data is coming from SQL Server. I used union and put both start and end dates in one column then set up a scaffold date. In addition I followed the instruction on here for an easy fix for the problem- no result! I also used parameters to dynamically change the length of the window in your solution, yet the end dates extend way beyond the window ends up until the process is completed. Can't believe something this simple becomes such an overwhelming headache. Any suggestion?
I mocked up something here.
I think the start and end dates need to stay in different columns
and then are joined to the scaffold as shown below.
The relative filter you described is applied to the scaffold date.
I also added a filter for dates less than Today.
Then I made some LODs to get the min and max dates in the selected window
and the created the Gantt as before.
One issue is that projects that are not in the window don't show up,
in your first version they were there but had a blank row. Is that ok?
Please see workbook and datasource attached in the Forum Thread.