I want a report that can compare story points from a historical project to story points from a current project. I want to be able to represent this as the “periods” of the project as my time frame.
Period 1 for historical Project A would be Jan 2016, period 2, Feb 2016 etc.
Period 1 for current Project B would be Mar 2017, period 2, April 2017 etc.
Then my report would show the story points for each project in period 1, period 2,period 3 etc.
How can this be done in MDX or eazyBI?
Hi,
In this case, perhaps you could try using the Lag function (to look 13 months ago) when creating a new calculated member in “Measures” dimension.
Try this code:
case when
[Time].CurrenthierarchyMember.Level.Name = "Month"
then
case when
[Project].CurrenthierarchyMember.Key = "PROJKEY" --key of archived project
then
nonzero((
[Measures].[Issues created],
[Time].CurrentMember.Lag(13) --13 months ago
))
else
[Measures].[Issues created]
end
end
Then you could filter your report and display the archived project as line in your chart
Kind regards,
Martins / eazyBI support team
Hi Martins,
I like your idea of using LAG. I assume I can lag to any amount of periods I want? I do see a complication with this method, though. If I do a LAG for like 8 month, wouldn’t me actual month of the second project be different that the first project I am comparing them too?
Hey Chuck,
I just found that there is no answer to your last question in this thread.
Yes, “Lag” would return the same month for every project in the report.
It takes the current month (when you run report) and then goes back to the lag parameter mathematically and it does not care that some projects started later or sooner.
Martins
