Set a start for cumulative data

jira
#1

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.

#2

Hi,

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

See also more about LAG function here:
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members#Calculatedmembers-Movingintime

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.

Martins / eazyBI support

#3

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)

Any advice?

#4

Hi,

In case of a daily trend, you could do the following

  1. Create a calculated member in “Time” dimension with a name “Days from last 3 full months”

    Aggregate(
    Filter(
    [Time].[Day].Members,
    DateBetween([Time].CurrentMember.StartDate,
    DateSerial(Year(Now()),Month(Now())-3,1),
    DateSerial(Year(Now()),Month(Now()),0)
    )
    )
    )

  2. Then create a cumulative measure

    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.

Martins / eazyBI support