I’m trying to create a report where the time ranges are custom irregular time periods. I created a calculated member in the Time dimension for each of these periods and this seems to work fine. Each calculated member’s formula is something like:
In the example above, my report needs to look at the value of Test for the first date in each of those date ranges and include that into the following calculations. However, using
[Time].CurrentMember.FirstSibling
seems to only bring back the first date of the month for each date. I also seem to be unable to use [Time].CurrentMember.NextMember or PrevMember to find the date before or after. I get the error message
“Failed to execute query. Error message:
Internal error: member [Time].[Aug 29 2019] not found among its siblings”
Can you tell me what I’m doing incorrectly? Thanks for your help!
Function FirstSibling understands the Time dimension’s hierarchy and helps to navigate through different hierarchy levels. In this case, you have Aug 29 2019 as the first date and function understands that siblings are at the month level, so the first sibling is Aug 1 2019, as in September dates it is September 1, at the calculated member level it doesn’t understand this level, so it returns All Times that is DefaultMember for Time dimension.
For you to get the first member of your calculated member, you need to think of those dates as a set of values. You can use the function to get the children set of you aggregated members and then the first item of this set.
With this in mind, it is possible to create a formula that calculates how many Issues were created at first date , but you can change measure ([Measures].[Issues created]) to different one accordingly to your needs:
Case WHEN
[Time].CurrentHierarchyMember.Level.Name <> "Day"
THEN
NonZero((
[Measures].[Issues created],
ChildrenSet([Time].CurrentMember).item(0)
))
END
I have a similar requirement . But rather than calculating the value for only the first member of the children set , I need to be able to do a cumulative sum of a measure up from first member up until the current member.
CASE :
Have created a calculated member within the Sprint Dimension as “Last 6 sprints” which gives an aggregate of last 6 sprints . Now , I need to be able to calculate the sum of story points resolved up until the sprint for which I’m calculating the data. For ex: If I am calculating data for the Sprint 1( first member of the measure "Last 6 sprint " )should give me the sum as equal to the story points completed for that sprint .
if i’m calculating the data for Sprint 5 the value should be sum of story points closed in ( Sprint 1 + Sprint 2 + Sprint 3 + Sprint 4 + Sprint 5 )
PURPOSE : Trying to calculate the average velocity for each sprint based on the story points completed in the previous sprints which are member of the measure “Last 6 sprints”
