Hi @bri8
Welcome to eazyBI Community! Thanks for posting this question!
I am very glad to hear you tried to do the research on your own to find an answer. Although this is not written in the documentation page, I have created a feature request to potentially look for possibilities to adjust the axis time more easily.
Otherwise, here is a solution I would like for you to try on your data and see if that works for you.
Your main “problem” is the date range, which in this case is reflected by Start and End dates. You may need to adjust the measure names (use the autofill functionality). Here is how my initial report looks. I created the same fields as you have to make it easier to understand the steps
This is the report I created with similar dates you provided in your table:
Step 1 : define 2 new measures for the Issue start date and issue end date. Based on you requirement. If the issue start date is older than previous month, then formula will return the date which was 1 month ago.
Similarly for the End date. If the End date is longer than 1 year from now, it will return the date which is going to be one year from now, other wise - it will return the actual End Date
Here is the Formula for “Start date”. I have named the measure “Check for Issue Start Date”
CASE
WHEN
DateCompare([Measures].[Issue Start Date],
"1 year from now") <= 0 AND
DateCompare([Measures].[Issue Start Date],
"1 month ago") < 0
THEN
DateParse("1 month ago")
WHEN
DateCompare([Measures].[Issue Start Date],
"1 year from now") <= 0 AND
DateCompare([Measures].[Issue Start Date],
"1 month ago") > 0
THEN
DateParse([Measures].[Issue Start Date])
END
Here is the formula “Check for End Date”
CASE
WHEN
DateCompare([Measures].[Issue End Date],
"1 year from now") >= 0 AND
DateCompare([Measures].[Issue End Date],
"1 month ago") > 0
THEN
DateParse("1 year from now")
WHEN
DateCompare([Measures].[Issue End Date],
"1 year from now") <= 0 AND
DateCompare([Measures].[Issue End Date],
"1 month ago") > 0
THEN
DateParse([Measures].[Issue End Date])
END
Step 2: define 3 new measures with the formula below. I’m only pasting the example for Milestone 1, but use the same formula, replacing “Milestone 1” with Milestone 2 and Milestone 3
CASE
WHEN
DateBetween(
[Measures].[Issue Milestone1],
"1 month ago",
"1 year from now"
)
THEN
[Measures].[Issue Milestone1]
END
Step 3. And lastly, you need to create a measure with formula below so you can apply a row filter on this measure. I have named the measure “Filter report”
CASE
WHEN
DateCompare(TimestampToDate(Max(
{[Measures].[Check for Issue Start date],
[Measures].[Check for End date],
[Measures].[Check for Milestone 1],
[Measures].[Check for Milestone 2],
[Measures].[Check for Milestone 3]
},
DateToTimestamp([Measures].CurrentMember.Value)
)
),
"1 month ago"
)
>=0
AND
DateCompare(TimestampToDate(Min(
{[Measures].[Check for Issue Start date],
[Measures].[Check for End date],
[Measures].[Check for Milestone 1],
[Measures].[Check for Milestone 2],
[Measures].[Check for Milestone 3]
},
DateToTimestamp([Measures].CurrentMember.Value)
)
),
"1 year from now"
)
<=0
AND
NOT IsEmpty([Measures].[Check for Issue Start date]) AND
NOT IsEmpty([Measures].[Check for End date])
THEN
1
END
Once you have applied the filter, you should remove it form your report, but the filter should remain active in the background.
This is how the final result might look:
I hope this is what you were looking for!
Best wishes,
Elita from support@eazybi.com
