How to show the Flow Efficiency of issues moved to X status in a particular month?

Hi there and Happy Friday!

I’m trying to build a report that would show a Flow Efficiency of issues moved to Live status per week / month. I was able to use the combination of “Workdays in transition status” and different “Transition Status” measures to display the time in each transition status for all issues in the last status (“Live”). From there, I was able to calculate the Flow Efficiency for each issues (see screenshot below). This is where I’m stuck currently. I’d like to plot the flow efficiency of issues that were moved to “Live” status in a particular week / month. However, when I use the Time dimension and plot the flow efficiency against weeks / months, the Flow Efficiency is only measured for Transition Statuses that an issue used in that week / month (see screenshots 2 with the right results and 3 when used with Time dimension). What I’d like to see is, for example:

When an issue was moved to the “Live” status in July 2023, it should capture the “Workdays in transition” for each Transition Status. It should then Calculate the sum of all workdays that the issue was in Active Transition States (defined as a calculated measure) and divide this number by the total number of workdays for all Transition States (defined as a calculated measure). This should give a Flow Efficiency measure. That measure should then be displayed in July 2023 month rather than any other month if the issue had been worked in in June and July.

Would anyone be able to help please? :slightly_smiling_face:

Hi, @arturc

I’m sorry for not answering earlier.

I would use the DateInPeriod to get the last date for the “Live” status for the Issue and use that to show the Flow Efficiency at the right time period.

If you are using the Issue dimension in the rows, the formula for Flow Efficiency should look something like this:

CASE WHEN
--Get the last "Live" status
DateInPeriod(
(
  [Measures].[Transition to status last date],
  [Transition Status].[Live],
  [Time].CurrentHierarchy.DefaultMember
)
  ,
  [Time].CurrentHierarchyMember
) 
THEN 
(
  (
  [Measures].[Workdays in transition status],
  [Transition Status].[Active Transition States],
  [Time].CurrentHierarchy.DefaultMember
)
  /
  (
  [Measures].[Workdays in transition status],
  [Time].CurrentHierarchy.DefaultMember,
  [Transition Status].DefaultMember
)
)
END


Please double-check the aggregated member in the Transition Status dimension “Active Transition Statuses” name. This is the member with all the Active statuses.

Let me know how it went.

Kindly,
Ilze support@eazybi.com

1 Like

Thanks for the response! I’ll test your idea in the next couple of days and report back :slightly_smiling_face:

Thanks a lot for the suggestion - your solution works perfectly!

1 Like