How to get the latest issue created date on time dimension

Questions & Answers
,
1

Hi,

I have one question on creating calculated members, the case details as below, please help, Thanks.

3 issues were created in 3 days.
The first issue was created at Jan 3rd 2020.
The second issue was created at Feb 1st 2020.
The third issue was created at Feb 20th 2020.

how to create the calculated member to get the latest date which there is issue was created? I want get the result like below ‘target Date’ member.

project Issue Created Date target Date
A Jan 3rd 2020
A Feb 1st 2020 Jan 3rd 2020
A Feb 20th 2020 Feb 1st 2020

please help , Thanks a lot!

Best Regards
Annie Gao

2

Hi Annie,

If you want to get the created date of the previous issue (issues are being sorted by created by default) try this code:

[Issue].CurrentHierarchyMember.PrevMember.get('Created at')

See the image below.

Martins / eazyBI support

prevmember created date
prevmember created date2894×1098 318 KB

3

Hi Martins,

Seems like the below code always show for all of issues…

but how can I get the target date which projects’ go online? like below:
project， current go online date， last go online date
A， Jan 3rd 2020,
A， Feb 1st 2020 , Jan 3rd 2020
A， Feb 20th 2020， Feb 1st 2020
B, Jan 4th,
B, Feb 8th, Jan 4th
C, Mar 10th
C, Mar 20th, Mar 10th

4

Hi,

In that case, make sure that you have imported your date-picker Jira custom field as property and measures in eazyBI and then use this formula (make sure you adjust the correct name for your imported measures)

CASE WHEN
[Issue].Currentmember.Level.Name = 'Issue'
Then
Order(
Filter(
[Issue].[Issue].Members,
[Measures].[Issues with baseline end date] >0 --check the name of measure
),
[Issue].Currentmember.Get('Baseline end date'),--check the name of cf
BASC
).item(
    Rank(
    [Issue].CurrentMember,
    Order(
    Filter(
    [Issue].[Issue].Members,
    [Measures].[Issues with baseline end date] >0 --check the name of measure
    ), 
    [Issue].Currentmember.Get('Baseline end date'), --check the name of cf
    BASC)
    )-2
).getdate('Baseline end date') --check the name of cf
End

Then you can see the previous baseline end date in for the specific project
Martins / eazyBI support

calculated measure
calculated measure1408×1416 130 KB

project baseline end date
project baseline end date2330×984 215 KB

5

Thanks a lot Martins.

The next I’m tring to do is to get the total issues which were created during near 2 go online date.

we can get first table as below:
project， current go online date， last go online date
A， Jan 3rd 2020,
A， Feb 1st 2020 , Jan 3rd 2020
A， Feb 20th 2020， Feb 1st 2020
B, Jan 4th,
B, Feb 8th, Jan 4th
C, Mar 10th
C, Mar 20th, Mar 10th

and we have second table as below:
project, time, issues created count
A,Jan 2nd, 2
A, Jan 25th,10
A, Feb 3rd,9
A, Mar 10th, 7
B, Jan 10th, 10
B, Feb 9th, 1
B, Feb 16th, 5
C, Mar 1st, 7
C, Mar 19th, 6
C, Mar22nd, 14

please help to create caculated member to get the issue created count for those issues created between 2 go online dates, target result table below:
Project, time,issue created
A， Jan 3rd 2020, 2
A， Feb 1st 2020 , 10
A， Feb 20th 2020， 9
B, Jan 4th,0
B, Feb 17th, 16
C, Mar 10th,7
C, Mar 20th,6

“time” is the go online date of this project
“issue created count” is the total issue count for those issues which created between prev go online date and current go online date for this project

please help to check , Thanks a lot

Best Regards
Annie Gao

6

Hi,

Is there some specific issue type for issues that you use to register the go online date in the project?
And please confirm that there are more than 1 issue in the project which have go online date on them!
If yes, can there be two identical go onine dates on issues in one project (for a reason or by accident)?

Martins

7

Hi,

We have many rounds system iterative upgrade with the same project id before this project be closed.

The specific issue type is “requirements”, we are trying to statistics how many requirements be delivered during each system/project iteration.

The ‘requirements’ should be counted in [time].[day] level and the line chart should be shown in [time].[month] level.

We’d like to get the sum issues for all of systems/projects, although systems/projects are go online in the different days of this month, they should be counted in the same month in chart.

It is complex scenes, please help
Thanks

8

You are correct, this case seems complex. Could you please send more specific details to support@eazybi.com
Please send

  • report definition exported to text file to better understand all report context (what dimensions would you use in rows, dimensions, which as page filters). This will be helpful in defining the specific calculated measure.
  • screenshots of how far you are and where the new measure is missing

Martins / eazyBI support

9

Hi Martins,

seems like it is working with below calulated member:
dimension is only time
measure is designed as below:
sum(
childrenset([project].currentHierarchyMember),
(
case when [measures].[go online count]>0 then
iif(
not sum({previousPeriods([Time].currentHierarchyMember),[time].currentHierarchyMember},[measures].[issue count])>0,
null,
sum(
filter(
[time].[day].members,
[measures].[issue count]>0 and not sum(previousPeroids([time].currentHierarchyMember),[measures].[go online count])>0
),[Measures].[issue count]
)
)
end
)
)

[measure].[issue count]:
(
[measures].[issues created count],
[issue type].[requirements]
)

please let me know if there is any more better way to do it, Thanks a lot for your help…

Best Regards
Annie Gao