I created a calculated field that evaluates if Date field is between (the larger of [User Start Date] parameter or [User End Date] parameter - 30 days) and User End Date. If so, return the date value from this record. I drop this alternate date field, which only contains dates between the specified range or 30 days, whichever is smaller, as a second axis and synchronize them. Then put on a ref band with Max and Min Date from this field, and hide the header.
See attached, using Order Date.
Dynamic Ref Line.twbx 428.9 KB
1 of 1 people found this helpful
See below my solution using Sample Superstore data.
1. Create a [Start Date] parameter
2. Create an [End Date] parameter
3. Create calculated field [Start Reference Band]
IF DATEDIFF('day', [Start Date], [End Date]) < 30 THEN [Start Date]
ELSE DATE(DATEADD('month', -1, [End Date]))-1
This calculated field will be used as the start of the reference band while the [End Date] parameter will be used as the end of the reference band.
4. Create a Boolean calculated field [Date Filter]
[Order Date] >= [Start Date] AND [Order Date] <= [End Date]
Put this on the filter shelf and set to "True" to limit the date axis to the Start and End Date parameter range. However, you can remove this if you would rather view the entire date range in your database.
For this to work, you need to put [Start Reference Band] on the Detail marks.
See attached workbook.
Hope this helps.
Hey Justin, thanks for your response. It did help me.
Thanks for the solution.