I have a calculated member as follows:
Cache(
sum({PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
[Measures].[Issues closed])
)
When I use this, the Cumulative data starts accumulating at the dawn of time. I would like to report this on a specific period, but I can not figure out the correct filter to only use data for the last 3 months. In other words, I i filter Time to only show the last 3 months, the value starts at 6000 in the first period shown when I want to start at 0.
I am not sure if I need to do something on the measure or if I can create a custom time member to only get data for the last three months.
Assuming that you use dynamic date filtering, you could try creating a new calculated measure that would calculate the cumulative results only for the last 3 months (see picture below)
Try this code:
CASE WHEN
DateAfterPeriodEnd(
[Time].CurrentHierarchyMember.Nextmember.startdate,
[Time].[Month].CurrentDateMember.Lag(2) --returns the month - 3 months ago
)
AND
DateBeforePeriodEnd(
[Time].CurrentHierarchyMember.StartDate,
[Time].[Month].CurrentDateMember
)
THEN
Cache(
sum({
[Time].[Month].CurrentDateMember.Lag(2): --returns the month 3 months ago
[Time].[Month].CurrentHierarchyMember
},
[Measures].[Issues created])
)
END
Actually, quite soon we are about to release a newer version of eazyBI where such calculations would be much easier. It is already being tested but I can’t share ETA at the moment.
This did not work when I tired to do a daily trend line for the three months. However I am very close.
I have created a calculated member for time:
Aggregate(
{Filter([Time].[Day].Members,
DateBetween([Time].CurrentMember.StartDate,‘3 months ago’,‘End of last month’))
})
I am then using this to calculate the cumulative measure:
CASE WHEN
DateAfterPeriodEnd(
[Time].CurrentHierarchyMember.Nextmember.startdate,
[Time].[Day].CurrentDateMember.Lag(
DateDiffDays(‘3 months ago’,‘today’)
)–returns the month - 3 months ago
)
AND
DateBeforePeriodEnd(
[Time].CurrentHierarchyMember.StartDate,
[Time].[Day].CurrentDateMember
)
THEN
Cache(
sum(
{ChildrenSet([Time].[Prior 3 Full Month Days]).Item(0):
[Time].CurrentMember},
[Measures].[Issues closed]
)
)
ELSE
0
END
The issue I have now is that I want the report to dynamically figure out what the last 3 fulls months are. I need to figure out ‘First Day of the Month 3 Months Ago’ in chronic lingo. I have tried many different ways, but can not get the time periods to start on December 1 (Today is Mar 28)
CASE WHEN
DateInPeriod(
[Time].CurrentHierarchyMember.startdate,
[Time].[Days from last 3 full months]
)
THEN
sum({
ChildrenSet([Time].[Days from last 3 full months]).item(0):
[Time].CurrentHierarchyMember
},
[Measures].[Issues closed]
)
END
But this calculation would not help for the Monthly trend.
CoalesceEmpty(
CASE WHEN
DateInPeriod(
[Time].CurrentHierarchyMember.startdate,
[Time].[Days from last 3 full months]
)
THEN
sum({
ChildrenSet([Time].[Days from last 3 full months]).item(0):
[Time].CurrentHierarchyMember
},
[Measures].[Issues closed]
)
END
,0)
How exactly did you get the screenshot with month-level if your calculated member “Days from last 3 full months” show day level when expanded? When I create exactly the same member in “Time” dimension and expand it, I get day level members. For your calculate member the calculated measure code would be different.
Please export and share the definition of your report
I have my original report below(Based on Issues created).
So by taking the reference of above report I need to create a new report based on the Cumulative count of issues created. and that report exactly looks like below.
and cumulative sum needs to start from April Month.
I hope my requirement is now clear to you.
Also I have attached my Original report definition with you. please check that and guide me accordingly.
Actually, we would recommend creating ratio as new calcualted measure with % integer format and organize your report in vertical position (then cumulative sum should work as expected from my previous post)