How do I get a specific value of a measure from an earlier time?

Questions & Answers
1

Hi all,

I am using the Cumulative Story Points completed measure in eazyBI to show project burn-up over time. I’d like to add some forward looking projections based on the story point completion rate over the past 6 weeks.

To do this, I need to have the story points completed today (easy) and the story points completed 6w ago as the starting point for the calculation but I am at a loss to find out how to get the completed story point value from 6w ago (highlighted below).

story_point_value_6w_ago
story_point_value_6w_ago382×518 7.84 KB

I thought it would have been as easy as creating a tuple of the ‘Cumulative Story Points completed’ measure and the date - 6w but nothing I’ve tried produces the output I want.

Something like this:

(
[Measures].[Cumulative Story Points resolved],
[Time].[Week].DateMember(DateAdd(‘ww’, -6, Now()))
)

I feel like there’s a simple solution here that’s just going over my head entirely. Does anyone have any ideas or examples to share?

Any help is much appreciated!

3

Found a working solution…this isn’t exactly it but it shows what I was going for next which was the total story points completed over 6 previous weeks.

I didn’t know you could use a colon between to members to create a set of members. ‘Lag’ is also new to me.

Sum(
  {[Time.Weekly].[Week].CurrentDateMember.Lag(6):
   [Time.Weekly].[Week].CurrentDateMember.PrevMember},
  [Measures].[Story Points resolved]
)
1 Like
4

Hi, @chrispy35

Welcome back to the eazyBI community.

Depending on what are your expected results, there are two different options:

  • If you are looking for one specific cell six weeks ago from now, then consider using this formula:
([Measures].[Cumulative Story Points resolved], 
[Time.Weekly].[Week].CurrentDateMember.Lag(6))
  • If you are looking to show continuous six weeks ago, then consider using this formula:
([Measures].[Cumulative Story Points resolved], 
[Time.Weekly].CurrentHierarchyMember.Lag(6))

Please see the difference in the picture below. Value 2611 is six weeks ago from now:

Cumulative Story Points Resolved
Cumulative Story Points Resolved1016×850 97.3 KB

Kind regards,
Ilze

5

Thank you Ilze. It’s good to see a clear example of the difference between the two different members of the [Time.Weekly] dimension.

One question I have though…I would expect the result of your first example to be 147 (W01 - 6 members = W48). Can you shed some experience on why it’s coming up with 2611?

6

Hi, @chrispy35

Oh, I see how it isn’t very clear, The print screen was very long, I ended it at W01, JAN 04.2021 :), you have to believe me that the value 2611 was 6 weeks from now :slight_smile:

Did you tried? How it went?

Kindly,
Ilze support@eazyBI.com

7

Thanks Ilze, my original problem is solved with your help.

PS - I’ve been watching some of your videos from Community Days and I’m learning a lot (and have much further to go).