Hi team,
I’m new with EazyBI and I’ve been struggling with something that it’s problaby easy for you guys. See the table below where I have aggregated issues (from Excel file) by the “Affected Service” dimension and their respective start and end dates. All I need is to calculate the difference (in days) between the issues, so I though to create a calculate measure using “datediffdays” with End Date property from the current member and Start Date property from the member below. In order to get the start date from the other issue, I’ve been trying to use “nextStartDate”, or to get the property using “nextmember” or “prevmember”, but it’s not working since I’m not using the Time dimension in the table and the member are grouped by a specific deimension, so the “nextStartDate” or “prevmember” wil get the date of next/prev from the issue ordered by created date and not the next one considering the aggregated issues and their Start and End dates field.
Please, any ideas on how to solve this?
Really appreciate your help guys!
You are correct that NextStartDate and PreviousStartDate work with Time dimension members only; in the Issue dimension, we need to find and look at the exact Prev or Next member or specified member in the set.
One option I wrote to you also in the e-mail would be to use the VisibleRowsSet() to find the previous issue from the issues on rows (I use Issue dimension and Issue created and resolved dates):
CASE WHEN NOT IsEmpty([Measures].[Issue created date])
THEN
Generate(
VisibleRowsSet(),
[Issue].CurrentMember
).Item(
Rank([Issue].CurrentMember,
Generate(
VisibleRowsSet(),
[Issue].CurrentMember
)
)
).get("Created at")
END
Thanks for your reply! That was exactly what I need and works well.
Just one point, in your example, I want to take the next member created date only of the members in the same aggregated dimension. See that the last member of “Epic” gets the date of the first member from “Data task”, but this last member shouldn’t take the next date of any member, since it’s the last one of its aggregated dimension (Epic). Are there any to do that, please?
I’ve tried here to do something like “CASE WHEN [Issue].CurrentMember.GetLinkedMemberName(‘Servico afetado’,[Serviço afetado].[Serviço afetado]) = [Issue].CurrentMember.Get(‘Servico afetado’)” since I imported the “Serviço afetado” as dimension and issue property, but it’s not working, it continues to get the date of next member from the other dimension.
You are correct; this is a valid formula filtering the Issues by Issues created to get the context:
CASE WHEN NOT IsEmpty([Measures].[Issue created date])
THEN
Filter(Generate(
VisibleRowsSet(),
[Issue].CurrentMember
), [Measures].[Issues created] > 0
).Item(Rank([Issue].CurrentMember,
Filter(Generate(
VisibleRowsSet(),
[Issue].CurrentMember
), [Measures].[Issues created] > 0)
)
).get("Created at")
END
Let me know if I missed something else and have some further questions!
Lauma / support@eazybi.com
Right, but I need to calculate the time difference between the issues, but considering the issues by aggregated dimension only. So, the last issue of the current aggregated dimension shouldn’t get the time of the issue from the next dimension (it mass the formula).
Is there any way to “ignore” the last issue from each aggregated dimension. At the end, I need to calculate the average of difference time creation between issues, aggregated by “Affected Services”.
I managed to get to the result I expected by using “datecompare” function.
It’s fine, but the problem now is that I need to calculate the average of " time difference between issues" grouped by “affected service” (aggregated dimension). So basically, for each “affected service” dimension, I need to sum the “time difference between issues” and divide by number of issues (from that dimension). I tried using the same formula I use to calculate “Average Duration” but for duration it calculate right (by each dimension) but for time difference between issues it calculate the average of all issues and not by each affected service dimension. Do you know why it happend and how I can get the average by each affected service dimension and not for all issues?
Just to updated on how I get the next member date only for the members of current aggregated dimension (Affected Service). I’m basically create a measure using “datacompare” function to compare the dates of “termino” and “next member start date”. If the “Termino” date was previus then “Next member start date”, it will return “1”. So I just perform the calculation when the “data compare” measure of each member is different then “1”. Follows the formula:
CASE WHEN [Measures].[date compare] <> 1
THEN
DateDiffMinutes([Measures].[Termino],[Measures].[Next member start date])
END
It works for me because in my scenario I don’t need to consider the time between issues when the next member issue is created before the current one.
