Dear Experts,
I am trying to create a formula which gives me JIRA issues due but two weeks ago.
We have the following formula:
/CumulativeSum([Measures].[Issues with due date])/
Sum(
{ PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember },
[Measures].[Issues with due date]
)
Now I thought I can easily create “Issues with due date two weeks ago” or something like give me calculated sum instead of current / today but “two weeks ago”
Anybody has an idea?
Welcome to the eazyBI community!
You are almost there with the “Issues with due date” measure and PreviousPeriods() !
If you want to calculate previous periods until two weeks before this week, you would use PrevMember
from the current week (CurrentDateMember), and won’t include additionally that in the time set:
Sum(
{PreviousPeriods([Time.Weekly].[Week].CurrentDateMember.PrevMember)},
[Measures].[Issues with due date]
)
Another option is using Lag(n) function which allows you to move back by n time periods in past: actually [Time.Weekly].[Week].CurrentDateMember.Lag(1) is the same as [Time.Weekly].[Week].CurrentDateMember.PrevMember; use Lag(n) to move back by longer periods (Lag(2) by two periods, Lag(3) by three, etc).
Best,
Ilze, support@eazybi.com
Great that worked very well and the correct value is displayed. However now its coming for every week, is there a chance to display it once only or the previous values as well? like a history?
Hi,
Yes, you may use the same approach with Lag(n) from the CurrentMember (the week in the row) to find a period (week in your case) before:
Sum(
{PreviousPeriods([Time].CurrentHierarchyMember.Lag(1)},
[Measures].[Issues with due date]
)
With Lag(n) (and PrevMember as well), used together with CurrentMember it always will take into account what periods do you have in rows: if you would have months in rows, it will look for previous months; if year - for previous years, etc.
Best,
Ilze