Date Filer for Epic Issues

Questions & Answers
1

I have read many posts from the great community and I know the answer I need is simple yet for some reason I keep getting a mental block and can’t piece together the solution.

Goal:
Looking to create a simple pie chart based on Custom dimensions imported. Lets call it Field A in the very simple table below. The real data has a lot of other filters being applied. It is just that pesky date that is caused the issue.

Issue Type | Field A | Target Start | Target End
Epic Car 1/1/20 1/1/21
Epic Bus 1/1/21 1/1/22
Epic Car 6/1/20 9/1/20
Epic Ca 1/1/20 3/20/20

I have set up my page filters to get rid of everything, and my row is “Field A” so I am getting back all of the data. My challenge is that I just want a set date range as shown below:

[Measures].[Issue End Date] >= ‘2021-04-01’
AND
[Measures].[Issue Start Date] <= ‘2021-06-30’

I have tried writing the Filter script and can’t figure it out. So any luck would be very appreciated. Here is one of my feeble attempts, and there are many others that also ended up disastrous :frowning:

Filter(
[Issue.Epic].[Epic].Members,
[Issue.Epic].CurrentHierarchyMember.StartDate <= ‘2021-06-30’
)

BTW - I did try to filter in the columns, but that doesn’t allow me to create a summary table that I can create a pie chart from.

Issues
created
All Change Drivers 51
Business Decision Analytics
Efficiency 4
Growth 11
New Regulatory 12
(none)
Operational Risk Reduction 7
RTB 7
Strategic Architecture 10

Looking for any help I can find.

Steve

2

Any ideas or suggestions?

3

Hi @GS_Steve,

When importing a custom date field, eazyBI creates a measure, like “Issues with Target end date”. Those measures work well with the Time dimension and represent issue count matching the date (Custom Fields - eazyBI for Jira).

Filter issues by two dates where each matching a different period are more complex and you might want to create a calculated measure in Measures. The calculation would go through epics and check on both the Target start date and Target End date.

Use functions Filter() and Descendants() to go through individual epics. And function DateCompare() to compare the target date with the specific date.

Sum(
  --set of Epics matching filter criteria
  Filter(
    --iterate through Epic issues
    Descendants([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Epic]),
    --for each validte that Start Date <= 2021-06-30
    DateCompare(
      [Measures].[Issue Start Date],
      "2021-06-30") <= 0 AND
    --for each validate that End Date >= 2021-04-01
    DateCompare(
      [Measures].[Issue End Daet],
      "2021-04-01") >= 0
  ), 
  --measure to count epic issues
  [Measures].[Issues created count]
)

Best,
Zane / support@eazyBI.com