Hi,
I’m trying to create a calculated field to derive “Target End Date” based on a combination of the Project attribute and availability of dates.
What I’m trying to do is something like:
If Project in (X, Y, Z) AND Sprint End Date is NOT NULL, then Sprint End Date
Else, If Project in (X, Y, Z) AND Issue Due Date is NOT NULL, then Issue Due Date
Else, If Project in (X, Y, Z), then Fix Version Due Date
Else, If Project not in (X, Y, Z), then Issue Due Date
And have this End date be populated for each issue based on the above criteria on a table.
Apologies in advanced for what could be a basic question, but I’m new to this and hoping the group can help provide guidance.
You need to create a new calculated measure in your Measure dimension. This formula will retrieve only new due date for your issues, to filter them by Time dimension add additional measure what you want to use in your report (issues created, issues resolved, or any other):
CASE WHEN -- If Project in (X, Y, Z)
Aggregate({
[Project].[Demo],
[Project].[Community Sprints]},
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember)) > 0
THEN
CASE WHEN -- Sprint End Date is NOT NULL,
[Measures].[Issue Sprint] <> "(no sprint)"
THEN -- then Sprint End Date
Filter(
[Sprint].[Sprint].Members,
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0).Item(0).get('End date')
WHEN -- Issue Due Date is NOT NULL
NOT IsEmpty([Measures].[Issue due date])
THEN -- then Issue Due Date
DateParse([Measures].[Issue due date])
WHEN -- Fix version is NOT NULL,
[Measures].[Issue fix versions] <> "(no version)"
THEN -- then Fix Version Due Date // Release date
Filter(
[Fix Version].[Version].Members,
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0 ).Item(0).get('Release date')
END
ELSE -- If Project not in (X, Y, Z)
-- then Issue Due Date
DateParse([Measures].[Issue due date])
END
