You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I am trying to build a report that will show me the average number of issues opened per week over the course of a date range.
For a complete year, I can do total (total issue count / 52 weeks), however life is not that easy. :)
I need to do (total issue count / (number of weeks between Oct 8 2018 and Dec 31 2018) which I could figure manually, but my reports tool should be able to generate my report without my outside math. :)
And then I also need to do (total issue count / (number of weeks YTD). Since the number of weeks are constantly changing in this one, I cannot hard code a value.
Everything I am finding is showing averages for durations between date fields on an issue, like average age or average time in status, but my divisor in this case is the number of weeks in the range, and I can’t find any examples, posts, or articles that even seem to hint at this, and nothing I can find is producing results that are anywhere near realistic.
Finally got it through a LOT of trial and error lol.
Avg( LastPeriods(12, [Time.Weekly].[Week].DateMember('2018-12-30')), CoalesceEmpty([Measures].[Issues created],0))
(Avg(LastPeriods(12, [Time.Weekly].[Week].DateMember(Now())), CoalesceEmpty([Measures].[Issues created],0)))