Create a measure for time since last release for Fix Version dimension

I have looked far and wide, and can’t get it to work. I need to create a report that shows all released Fix Versions (Status = ‘Released’) from most recent to the oldest and calculate time since previous release for each row.

I want this difference to only be calculated between Fix Versions displayed in the report, but the results I receive (using prevmember, nextmember, lag, lead functions) seem to always be calculated using the order of Fix Versions in the database, and not in the report. How can I solve this?

I tried sorting the set of Fix Versions in a calculated member

Aggregate(
Order(
  Filter(
    [Fix Version].[Version].Members,
    --filter by both, status and name
    [Fix Version].CurrentMember.get('Status') = "Released"
  ), [Fix Version].CurrentMember.Get('Release date'), DESC
  )
)

But when I try to calculate de time distance between current member and nextmember the result doesnt seem to match the date of the closest previous release:

DateDiffDays(
    [Fix Version].CurrentMember.NextMember.Get('Release date'),
    [Fix Version].CurrentMember.Get('Release date')
    )

Hi @Dawid_Roszkiewicz,

Ordering versions for each report row might be pretty complex and time-consuming.
You can compare the values of two sequential rows using the function PreviousRowValue().
If the latest version is on top of the report, then the expression might look like this:

DateDiffDays(
  [Measures].[Version release date],
  PreviousRowValue([Measures].[Version release date])
)

Here are more details on the mentioned function: PreviousRowValue

Best,
Zane / support@eazyBI.com

1 Like

Thank you for the solution, that’s exactly what I was looking for. :slight_smile:

1 Like