How to associate changing team size with sprint data

Questions & Answers
1

I created a CSV with Team Size by Date and loaded it as a source file into the same cube with my Jira Software sprint data. I’d like to use the start date of a Sprint to find the corresponding team size, and then use that in calculations.

First is the "Cumulative Team Size’ measure, defined as the following:

[Measures].[Cumulative New Hires] =
Cache(
Sum({PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
[Measures].[New hires]
)
)

This correctly works with the time dimension to produce the expected groupings for the past 5 years of data.

So then I tried to get this to work with Sprint dimension data in a calculated measure that uses a tuple with the second argument being the time dimension to force it to always be the Start date of the Sprint for the current cell.

image
image1238×562 46.4 KB

It doesn’t work and produces nothing for each Sprint, where I know the dates should produce something. Hard to debug what’s going wrong. I tried changing the second argument of the tuple to just lookup a known date and that didn’t work either, so thinking I’m doing something fundamentally wrong.

Any suggestions?

Thanks,
Mike.

2

I guess there’s something special about the [Sprint] dimension – it does not have a DefaultMember so the Cumulative New Hires measure was not finding a unique cell in the cube.

Fix is to force it in the tuple:

image
image1238×560 59.7 KB

Thanks,
Mike.

1 Like
3

Hi @mbeynon,
great to hear that you found the solution!
For others that are checking this question - in additional mapping data is mapped only to the information that is specified in the mapping, to all other information it is mapped to the Default member and that needs to be referenced in the formula.

@mbeynon, one improvement that you can do in your cumulative formula is to use the DefaultContext(), so your measure is more flexible.
Like this:

Cache(
Sum({PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
DefaultContext((
[Measures].[New hires], 
[Time].CurrentHierachyMember
))
)
)

best,
Gerda // support@eazybi.com