“Estimated EndDate” is a custom field that contains an estimated completion time for when the story will end.
I want to compare the “Estimated EndDate” values entered in the story at the end of each sprint, but the table below only shows the most recently entered value.
I want to make it behave like the “Sprint Story Points at closing” value, but I can’t do that with the custom field “Estimated EndDate”?
I’ve read and followed the link below, but it’s still not working, please help!!!
Property “Issue Estimated EndDate” will return the current value of a date picker but you can import the history for date field timestamps and then return the last value at certain date.
See similar question answered here: How can i show the change history of date custom field?
You can impport the timestamps of date picker field changes and then calculate the “End date history” from these changes.
Once the history is imported and the calculated measure is created, you can try creating the final calculated measure (with Month Day Year format) to retrieve the historical date picker field value at the sprint actual end date.
CASE WHEN
Not IsEMpty([Measures].[Sprint actual end date])
THEN
Sum((
[Time].[Day].DateMember(
[Measures].[Sprint actual end date]
),
[Measures].[Estimated Enddate history] --calculated measure from community example
))
END
Martins / eazyBI support
I proceeded in the following order
[jira.customfield_test_date_history]
name = "Test Date History"
data_type = "decimal"
measure = true
multiple_dates = true
javascript_code = '''
dateChangeStrings = [];
whenDateChanged = issue.fields.created; //.toString().substr(0,10);
newDateChange = null;
duedateAsTimeChangeStamp = null;
issue.changelog.histories.forEach(function(history){
history.items.forEach(function(historyItem){
if (historyItem.field == "Test Date" ) {
newDateChange = historyItem.from;
if(newDateChange){
duedateAsTimeChangeStamp = Date.parse(newDateChange);
dateChangeStrings.push(whenDateChanged.toString().substr(0,10) + "," + duedateAsTimeChangeStamp/1000);
dateChangeStrings.push(history.created.toString().substr(0,10) + "," + -duedateAsTimeChangeStamp/1000);
}
whenDateChanged = history.created;
}
});
});
if (issue.fields.customfield_10602) {
dateChangeStrings.push(whenDateChanged.toString().substr(0,10) + "," +
Date.parse(issue.fields.customfield_10602)/1000) ;
}
issue.fields.customfield_test_date_history = dateChangeStrings.join("\n");
'''
[Measures].[Issue Test Date History]
CASE WHEN
Not IsEMpty([Measures].[Sprint actual end date])
THEN
Sum((
[Time].[Day].DateMember(
[Measures].[Sprint actual end date]
),
[Measures].[Test Date History]
))
END
The Test Date History column and Calc column are only visible in Sprint 4.
The [Measures].[Issue Test Date History] values are displayed in each Sprint.
I think I’m typing something wrong, but I can’t figure it out. Can you help?
Hi,
All is good.
The “Test Date History” measure currently returns results just for sprints without the sprint’s actual end date due to the Case WHEN statement logic.
If you need to show it for all sprints, try the calculated measure without CASE structure.
“Issue Test Date history” is just a property of an issue, and it doesn’t care for other dimensions but “Issue”. That is why it returns results for all sprints as long as the issue is in rows or report context.
Martins / eazyBI
Hi~!
Thank you so much for your quick response
I changed the code to look like the one below as you mentioned.
Sum((
[Time].[Day].DateMember(
[Measures].[Sprint actual end date]
),
[Measures].[Test Date History]
))
The result is shown below.
We want the values that changed in each Sprint to be output as shown by the red line in the image above. Is there any other way to do this?
I’ll keep my fingers crossed and wait for a response.
Again, thanks for the quick response.
Hi,
Can you add “Sprint issues added” and “Sprint issues committed” measures to the report and share the screenshot from report again?
Martins / eazyBI
Hi,
Try this formula for your calcualted measure:
Sum(
Filter(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Sprint issues committed]>0
),
Sum((
[Time].[Day].DateMember(
[Measures].[Sprint actual end date]
),
[Measures].[Test Date History],
[Sprint].Currenthierarchy.DefaultMember)
)
)
Make sure you select the “integer” format for this calculated measure.
Martins / eazyBI
Hi,
Thanks for showing me how to do it quickly.
However, I am getting an error as shown below.
Could it be related to the plugin version?
(We are using version 6.4.1)
Thanks.
@Jae-Kou_Lee
Check the updated code in my latest answer.
There was a syntax error earlier.
Martins / eazyBI
Hi,
The code you modified works fine.
I’m so happy to see the changes you made in each sprint.
Now we have one last hurdle.
If I change the Formatting to Date / Time yyyy-mm-dd ISO format date, the value is displayed as yyyy-mm-dd as shown below.
(Everything under Date / Time is the same)
Did I set the formatting incorrectly?
Thanks.
Currently the result is calculated as timestamp which is a number and expects either “decimal” or “integer” output format.
If you want to display it as date, you would need to use the formula TimestampToDate and then select one of the “Date/Time” output formats for your calculation.
Try this formula with Date/Time format.
TimestamptoDate(
Sum(
Filter(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Sprint issues committed]>0
),
Sum((
[Time].[Day].DateMember(
[Measures].[Sprint actual end date]
),
[Measures].[Test Date History],
[Sprint].Currenthierarchy.DefaultMember)
)
))
Martins / eazyBI
Hi,
Your guide was perfect.
My report is going to shine thanks to you.
I made one change that I thought I’d share.
When I tried the script in the link, it gave me different values for the last sprint I changed.
So I changed the script to the following and it worked fine.
dateChangeStrings = [];
whenDateChanged = issue.fields.created; //.toString().substr(0,10);
newDateChange = null;
duedateAsTimeChangeStamp = null;
histSet = {};
issue.changelog.histories.forEach(function (history) {
history.items.forEach(function (historyItem) {
if (historyItem.field == "Test Date") {
newDateChange = historyItem.from;
if (newDateChange) {
duedateAsTimeChangeStamp = Date.parse(newDateChange);
strNewDate = whenDateChanged.toString().substr(0, 10);
histSet[strNewDate] = strNewDate + ',' + duedateAsTimeChangeStamp / 1000;
}
whenDateChanged = history.created;
}
});
});
if (issue.fields.customfield_10602) {
strNewDate = whenDateChanged.toString().substr(0, 10);
histSet[strNewDate] = strNewDate + ',' + Date.parse(issue.fields.customfield_10602) / 1000;
}
for (var histDate in histSet) {
dateChangeStrings.push(histSet[histDate]);
}
dateChangeStrings = dateChangeStrings.sort();
issue.fields.customfield_test_date_history = dateChangeStrings.join("\n");
Once again, thanks for your help.