It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to calculate 3 week moving average using eazyBI

Bernie Carson Jun 19, 2017

I'd like to create a plot that is updated daily with a 3 week moving average of how many story points are resolved. Mechanically this would mean looking at 3 weeks into the past and finding what the average story points resolved per day is and having that be one data point. Then the next day the same information would be pulled and a new average would be found and that would be the next data point and so forth conitnuously. If anyone has any insight on how to do this with JIRA dashboards or eazyBI it would be much appreciated. I haven't been able to find data the automates moving average graphs in connection with JIRA.

 

1 answer

1 vote
Zane Baranovska Jul 18, 2017

Hi,

When time hierarchy level in a report is days, then this calculation will do the work:

Avg(
  LastPeriods(21, [Time].CurrentHierarchyMember),
  [Measures].[Story Points resolved]
)

When time hierarchy level in a report is weeks, then you may use 

Avg(
LastPeriods(3, [Time].CurrentHierarchyMember),
  [Measures].[Story Points resolved]

More about dynamic time periods (including function LastPeriods() ) you may find in the eazyBI documentation.

 

Zane
eazyBI Customer Support Consultant

@Zane Baranovska I want to tweak this to return me the same average but instead of story points, I'd like an issue count of resolved issues. Using the above code and swaping story points resolved for issue count only returned me the full count, and not the average. can you assist?

Zane Baranovska Mar 13, 2018

@John-Michael Talarico, your approach to change only the measure is correct.
For example, to calculate a three-week moving average for resolved issues, you may use measure Issues resolved and put it in a formula like this:

Avg(
LastPeriods(3, [Time].CurrentHierarchyMember),
[Measures].[Issues resolved]
)

Make sure that in Time dimension is selected the right hierarchy over which you would like to get running average (see picture below).

eazyBI_3_week_moving_avg_resolved_issues.png

 

 

Zane / support@eazybi.com

Thanks! this is great! I'll be working a lot more with the tool. Is the best means on contact through email or tagging eazyBI here?

Zane Baranovska Mar 15, 2018

You may use both channels. eazyBI Support is checking questions in Atlassian Community pages as well.

The community is a great power: if you ask the question there, you may get an answer from other experienced users, and this answer would benefit other users as well. Also, you may google the topic later, while, in your mailbox, it would get lost.

If you have a question specific to your business case or with sensitive information, you may reach eazyBI support via email.

Larry Lowe Oct 10, 2018 • edited

@Zane Baranovska i tried the above solution and i ran into problems with reporting an accurate weekly average. Even if you look at the above image from March 13, 2018... Week 10 has 1 issue resolved, and an average of 1... Week 11 has 0 resolved, but the average is still 1... Week 12 has 0 resolved, and the average is still 1... by this point, the average should be 1/3 or 0.333.

Is this because the resolved values are blank instead of actually saying 0? Is there a formula to swap out blank values for 0?

Zane Baranovska Oct 15, 2018

@Larry Lowe your guess is right. Empty values are not taken into account for average calculation. To make periods without resolved issues count as well you may use function CoalesceEmpty() in the formula like this:

Avg(
  LastPeriods(3, [Time].CurrentHierarchyMember),
  --count as zero if no resolved issues in a period
  CoalesceEmpty([Measures].[Issues resolved],0)
)


Best,
Zane / support@eazyBI.com

Larry Lowe Oct 15, 2018

@Zane Baranovska Thank you this works perfectly!

Shenghan Gao Apr 15, 2019

How can I make this `Last 2 week` thing not depend on selected time dimension? I tried the following but it still gives 4 days average when the hierarchy is daily.

```

Avg(
LastPeriods(3, [Time.Weekly].CurrentHierarchyMember),
[Measures].[Issues resolved]
)

```

Roberts Čāčus May 08, 2019

Hi @Shenghan Gao

 

Please try to use the DateMember() function to get a specific Time dimension level member from the one you have currently selected. Please have a look at the formula below:

[Time.Weekly].[Week].DateMember([Time].CurrentHierarchyMember.StartDate)

In the first part you define the Time dimension hierarchy and the level you want to use and in the DateMember() function specify the members start date from which to get the Week member. In this case the current hierarchy member.

The formula for the average calculation could look like this:

Avg(
LastPeriods(3, [Time.Weekly].[Week].DateMember([Time].CurrentHierarchyMember.StartDate)),
[Measures].[Issues resolved]
)

 

Roberts / support@eazybi.com

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in Marketplace Apps & Integrations

Demo Den Ep. 4: Continuous Integration with CircleCI & Jira Software

Get ready! Demo Den Episode 4 is coming your way on Tuesday, May 28, 2018 with a Continuous Integration and Delivery special demo. CircleCI Director of Solutions Engineering, Eddie Webb will show us ...

449 views 0 4
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you