How Best to Separate Task Count All Previous Months vs Current Month

Questions & Answers
1

Hello, I have a report that I’m displaying a count of statuses per each month of the year but I need to revise one of the measures to instead of showing open all throughout the year, show a count of what was being worked on and what is being worked on. Therefore, as of now Jan to Sep should show a count of what was being worked on and Oct should show a count of what is being worked on. But my attempts for this are not successfully as the last image shows the purple and orange measures give a count for every month of the year. How can I correct these?

image
image734×354 16 KB

image
image734×354 15.9 KB

image
image1523×607 33.9 KB

2

Hi @Alyssa_A

Try using the case statements.

This formula for “What was being worked on”

CASE WHEN
DateAfterPeriodEnd(
  'yesterday',
  [Time].CurrentHierarchyMember
)
THEN
Aggregate({
[Transition Status].[Analysis],
[Transition Status].[In Progress],
[Transition Status].[At Risk]
},
[Measures].[Transitions to status issues count]
)
END

And this formula for “What is being worked on”

CASE WHEN
DateBeforePeriodEnd(
  'today',
  [Time].CurrentHierarchyMember
)
THEN
Aggregate({
[Transition Status].[Analysis],
[Transition Status].[In Progress],
[Transition Status].[At Risk]
},
[Measures].[Transitions to status issues count]
)
END

Martins / eazyBI

3

Thank you, this corrected the separation in the time that I needed. I’m not getting the counts that I expected and it’s making me realize I must be using the wrong measures to count the statuses the way I need. Can you help me with this as well?

I’m looking for it to count the tasks that were/are in an ‘Analysis’, ‘In Progress’, or ‘At Risk’ status any at point in every month. For example, if a task transitioned to ‘Analysis’ in Jan 2022 and remained in that status for Feb 2022 then transitioned out of that status in Mar 2022 then that task should be counted towards Jan, Feb and Mar. How can I accurately do this?

4

I changed a portion of both measures to the following. This is closer to the count I need but the ‘what is being worked on’ is now showing for future months when it shouldn’t. How do I correct that?

Aggregate({
[Status].[Analysis],
[Status].[In Progress],
[Status].[At Risk]
},
[Measures].[Issues history]
)
END

image
image1583×440 23.2 KB

5

Try this formula for “What is being worked”

CASE WHEN
DateInPeriod(
  'today',
  [Time].CurrentHierarchyMember
)
THEN
Aggregate({
[Transition Status].[Analysis],
[Transition Status].[In Progress],
[Transition Status].[At Risk]
},
[Measures].[Issues history]
)
END

please find the definition for “Issues history” measure
https://docs.eazybi.com/eazybi/data-import/data-from-jira/import-issue-change-history#Importissuechangehistory-Measures

How many issues at the end of selected Time dimension period are

  • in status that is selected in Transition Status dimension (calculated as Transitions to status minus Transitions from status from beginning of time until the end of the selected Time period);

It won’t count both scenarios issues that went and issues was in the status.
If you need to count both sceanrios (those that went to status and those that already are) you need some custom user-defined measure

Martins / eazyBI

1 Like