Creating a Calculated Date Member from 2 Transition Dates

Questions & Answers
1

Hello!

I was wondering is there a way to consolidate date from different transition statuses?

The Jira project we are working on updated the statuses in the workflow. I’m trying to get a true “In Progress” date based on 2 transitions:

  • Old Status: “In Progress”
  • New Status: “Committed & In Progress”

We have a specific date as to when tickets got moved over to the new “Committed & In Progress” status. So i was thinking if we can combine the 2 dates using a CASE WHEN statement, but I just can’t figure it out.

Case when status “In Progress” last date is before 09/10/2021 then display “In Progress” last date.
If “In Progress” last date is null, then display “Committed In Progress” last date.

These are the 2 measures I was able to create:
In Progress Last Date

    ([Measures].[Transition to status last date],
     [Transition Status].[In Progress],
     [Time].CurrentHierarchy.DefaultMember)

Committed In Progress Last Date

    ([Measures].[Transition to status last date],
     [Transition Status].[Committed & In Progress],
     [Time].CurrentHierarchy.DefaultMember)

Thank you!

2

Hi,

We can rewrite your definition of the calculation as an MDX formula:

CASE WHEN
  DateCompare(
  ([Measures].[Transition to status last date],
     [Transition Status].[In Progress],
     [Time].CurrentHierarchy.DefaultMember),
     DateParse("Sep 10 2021"))<0
 THEN

 TimestampToDate(
 ([Measures].[Transition to status last timestamp],
     [Transition Status].[In Progress],
     [Time].CurrentHierarchy.DefaultMember))
 ELSE
 TimestampToDate(
   CoalesceEmpty(
     ([Measures].[Transition to status last timestamp],
     [Transition Status].[In Progress],
     [Time].CurrentHierarchy.DefaultMember),
     ([Measures].[Transition to status last timestamp],
     [Transition Status].[Done],
     [Time].CurrentHierarchy.DefaultMember)
   )
   )
  END

I hope, that helps to finalize the implementation of the measure.

Kindly,
Janis, eazyBI support