Calculated Measure for last priority transition date (multiple values)

Questions & Answers
1

Hi

I have a report that shows the average time between the date the priority was set to Highest and the delivery date.

I would like to propose a filter allowing to choose:

  • either Highest priorities
  • either High and Highest priorities
  • either Medium, High and Highest priorities

This does not work as desired in one case : if an issue is

  • created in High on 01 jan 2024,
  • then upgraded to highest on 02 jan 2024
  • and delivered on 03 jan 2024

Expected :

  • with the filter on Highest priorities, I would like the calculated duration to be 1 day (from 02 jan 2024)
  • with the filter on the High and Highest priorities, I would like the calculated duration to be 2 days (from 01 jan 2024). However, in this case, it is again the date of 02 jan 2024 which is retained

Is there a way to change my calculated measure [Date transition priorité sélectionnée] so that transitions between two priorities included in my filter are not taken into account?

Here are my calculated measures :

Date transition priorité sélectionnée

TimestampToDate(
   (
     [Measures].[Transition to last timestamp],
     [Transition Field].[Priority],
     [Priority].CurrentHierarchyMember,
     [Time].CurrentHierarchy.DefaultMember
   )
)

Nb tickets livrés

NonZero(
  Count(
    --set of issues
    Filter(
      --iterate through individual issues
      DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
      --check if first ever status change to Customer Approval was in the selected period
      DateInPeriod(
        (
          [Measures].[Transition to status first date],
          [Transition Status].[Recette client],
          [Time].CurrentHierarchy.DefaultMember
        ),
        [Time].CurrentHierarchyMember)
    )
  )
)

Durée avant livraison en recette (en J.O.)

CASE WHEN 
(
  [Measures].[Nb tickets livrés]
) > 0 
THEN
 AVG(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      (
        [Measures].[Nb tickets livrés]
      ) > 0 
    ),
    
    case WHEN
    DateToTimestamp
    (
      (
        [Measures].[Date transition priorité sélectionnée],
        [Time].CurrentHierarchy.DefaultMember
      )
    )
    <
    DateToTimestamp
    (
      (
        [Measures].[Transition to status first date],
        [Transition Status].[Recette client]
      )
    )
    THEN
    DateDiffWorkdays(
      (
        [Measures].[Date transition priorité sélectionnée],
        [Time].CurrentHierarchy.DefaultMember
       ),
      (
        [Measures].[Transition to status first date],
        [Transition Status].[Recette client]
      )
    )
    END
  )
END

Here are the calculated members of the Priority dimension

[Piority].[Urgents]

Aggregate(
  {
    [Priority].[Highest]
  }
)

[Piority].[High et +]

Aggregate(
  {
    [Priority].[High],
    [Priority].[Highest]
  }
)

[Piority].[Medium et +]

Aggregate(
  {
    [Priority].[Medium],
    [Priority].[High],
    [Priority].[Highest]
  }
)
2

Hello @ThomasLegrand,

Thanks for posting your question!

You are looking in the right direction, you just need to change “Transitions to last timestamp” to “Transitions to first timestamp” in your code below.

TimestampToDate(
   (
     [Measures].[Transition to first timestamp],
     [Transition Field].[Priority],
     [Priority].CurrentHierarchyMember,
     [Time].CurrentHierarchy.DefaultMember
   )
)

Measure “Date transition priorité sélectionnée” will then return the date when the issue first transitioned to any of the priorities selected in the report.

I hope this helps!

Best,
Marita / support@eazybi.com

3

Thank you very much for your answer @Marita_Norvele

I would like the last date and not the first.
It happens that an issue has a High priority, before being lowered to a Medium priority. Then it goes back to High (or Highest), and it is the duration between this last modification and the delivery date that I want to measure.

An example :

  • Jan 01, 2024: issue created (High)
  • Jan 02, 2024: priority changed to Medium
  • Feb 01, 2024: priority changed to High
  • Feb 02, 2024: priority changed to Highest
  • Feb 03, 2024: issue delivered

If I filter on Highest, I want it to return 1 day.
If I filter on High and Highest ([Piority].[High and +]), I want it to return 2 days (between Feb 1 and Feb 3).

Regards

Thomas

4

Hello @ThomasLegrand,

Thanks for the additional explanation.

In that case, I suggest to use the following code (explanations given in the comments of the code):

CASE WHEN
 [Measures].[Issues created]>0
THEN
 DateDiffDays(
    TimestampToDate(
     CASE WHEN
    --corresponds to current priority
     [Measures].[Issues created]>0
    THEN
     CASE WHEN
    --check if there were transitions from 
    --all transitions to priority
      ([Measures].[Transitions to],
      [Transition Field].[Priority],
      [Priority].DefaultMember)
      >
    --transitions to this priority  
      ([Measures].[Transitions to],
       [Transition Field].[Priority])
     THEN
    --checks the last cycle after the transition from 
       Aggregate(
         [Time].[Day].DateMembersBetween(
    --from the day it last moved to any other priority
           TimestampToDate(
             Aggregate(
    --set of other priorities
               Except(
    --set of all priorities
               [Priority].[Priority].Members,
    --set of selected priorities
               DescendantsSet(
                 [Priority].CurrentHierarchyMember,
                 [Priority].[Priority]
               )),
              ([Measures].[Transition to last timestamp],
               [Transition Field].[Priority])
             )
           ),
    --until the resolution date
        [Measures].[Issue resolution date]
         ),
    --looking for the first transition to the selected priority 
      ([Measures].[Transition to first timestamp],
       [Transition Field].[Priority])
       )
     ELSE
    --taking into consideration one cycle
      ([Measures].[Transition to first timestamp],
       [Transition Field].[Priority])
     END
    END),
   [Measures].[Issue resolution date] 
 )
END

Let us know if you have any additional questions.

Best,
Marita // support@eazybi.com