Hi! I need to be able to get the average of the results I get from the following formula:
CASE WHEN
[Measures].[Status.Category] = [Status.Category].[En curso]
THEN
Cache(
– days in transition status when issue was in this status previously
[Measures].[Days in transition status] +
– days since last transition to this status
IIF([Measures].[Issues history] > 0,
DateDiffDays(
( [Measures].[Transition to status last date],
[Time].CurrentHierarchy.DefaultMember ),
Now()
), 0
)
)
ELSE
Cache(
– days in transition status when issue was in this status previously
[Measures].[Days in transition status])
END
Please! i need your help 
Hi @Marco_Martinez
Welcome to the eazyBI community!
What is the layout of your report?
What dimensions do you use in report rows and page filters?
How would you calculate the average? Is it by issues, average by time or perhaps by another dimension?
Martins / eazyBI
Hi @martins.vanags
we created a new level in Jira called “Iniciativa”, this level is above the epics.
In this report what we need to show is the time spent in the transitions of each task that are related to the initiatives in the time period of one week.
We currently use this formula (“Tiempo_Transición”):
([Measures].[Days in transition status],
[Transition Status.Category].[Ready]) +
([Measures].[Days in transition status],
[Transition Status.Category].[In progress])
However, we have detected that it brings us information of tasks that did not necessarily have a transition during the week we reviewed.
This is how we have constructed the dimensions and columns of the report.
@Marco_Martinez
What steps did you do to discover that some wrong tasks are used for calculations in the report?
Martins / eazyBI
@martins.vanags
What I am doing is looking at the details of the activities that are being considered for the number of days:
Looking at the list, for example, this pbi for the week of May 01 has 21.77 days, upon review I have detected that the transition to in progress is April 12, and the week we are reviewing only two transitions have occurred, the first is from in progress to codereview and from codereview to review. Therefore, shouldn’t the report only consider the time that has elapsed during that week and not count since the pbi transitioned to in progress, which in this case is 21.77?
Maybe I’m wrong in my logic so if you can help me to clarify I would be very happy 
@Marco_Martinez
If your formula for “Tiempo_Transicion” is calculated from measure “Days in transition status”, and it will be displayed against the period when the issue leaves the transition status. The result will be calculaed as difference between the day it entered (in previous periods) the status and left the status in the particular period.
That is why it can bee that issue remained for 21 days in the status before the status was changed on w15.2023
Martins / eazyBI
