What is the calculated measure to count issues that are due in the next 7, 14, 30, 60 days and after 60 days?
Attaching a screenshot here for the desired output.
Hi @pintu,
You can use measure “Issues with due date” together with the “Time” dimension to see how many issues have a due date set for each period or day.
-
To make a report more flexible and group future periods in relative groups “Next 7 days” or “Next 7 to 14 days”, you might want to define those groups in the “Time” dimension using calculated members.
For example, to group the date of the next 7 days, go to the “Time” dimension and define a new calculated member. The calculation will aggregate days from today till 7 days from now. The calculation might look like this:Aggregate( [Time].[Day].DateMembersBetween('today', '7 days from now') )For more details on calculated members in Time dimension and relative date expression please see the documentation:
-
Set the “Status” dimension on report pages and select status categories “To Do” and “In Progress” to filter on unresolved issues for the report.
The report might look like in the picture below.
Best,
Zane / support@eazyBI.com
Thank you Zane. Can you give me an example for
-
Next 7 to 14 days
-
60+ days
-
overdue count
For Next 7 to 14, I basically did [Time].[Next 14 days] - [Time].[Next 7 days], is this efficient?
Hi @pintu ,
I suggest you build on top of the Time dimension calculated member Zane shared with you. For the Next 7 to 14 days you can use a similar approach:
Aggregate(
[Time].[Day].DateMembersBetween('7 days from now','14 days from now')
)
The approach is described well in the documentation page shared before - Date filters.
For 60+ days try the formula below:
Aggregate(
Filter(
[Time].[Day].Members,
DateCompare(
[Time].CurrentMember.StartDate,
[Time].[Day].CurrentDateMember.Lead(60).StartDate
) > 0
)
)
For overdue issues, see a report from our Demo account - Issues due and overdue - Issues - Jira Demo - eazyBI.
Best,
Roberts // support@eazybi.com