I have a report that is supposed to display the differences between the two previous months; in this case July subtracted from June. I have a calculated member that I’m using with the following equation:
[Time].[Month].DateMember(‘1 month ago’) - [Time].[Month].DateMember(‘2 months ago’)
In some cases the math is correct, but in others it is subtracting the number from May with June. Am I over simplifying this? I feel like this should be a fairly easy/simple equation.
Hi @mwolfe_infpnt,
In the Time dimension and other dimensions, you may create calculated members that aggregate (group) those dimension members. But mathematical operations, like subtraction, are not supported in calculated members. If you want to perform some mathematical operations, please create a calculation in Measures.
For more details, please see the Ground rules here: Calculated measures and members - eazyBI for Jira.
in the “Measures” section, define separate calculated measures for comparing changes in previous periods
For example, to calculate the difference between the current and previous period, you may use an expression like this:
CASE WHEN --for a current and previous period
DateInPeriod('today',[Time].CurrentHierarchyMember) OR
DateInPeriod('today',[Time].CurrentHierarchyMember.NextMember)
THEN --compare monthly value with the previous period
[Measures].[Total In Tracker]
-
([Measures].[Total In Tracker],
[Time].CurrentHierarchyMember.PrevMember)
END
Best,
Zane / support@eazybi.com