Filer Time in Fix Version "by name"

Questions & Answers
4

Hi @lennoncork!

Here is a generic solution to get the Time between the first start date and last release date (returning 1 for the period between the dates) for either Fix Version default or By Name hierarchies, no matter if single or multiple versions are selected

CASE WHEN
 DateBetween([Time].CurrentHierarchyMember.StartDate,
 Order( 
    Filter(
      Generate( -- here we generate a set of Fix Versions either from Fix Version hierarchy or aggregated Page selection
        { [Fix Version].CurrentHierarchyMember,
        ChildrenSet([Fix Version].CurrentHierarchyMember) },
        -- The lowest level in Fix Version dimension is Version or project in hierarchy by name
        IIF([Fix Version].CurrentHierarchy.Name = "Fix Version.By name",
         Descendants([Fix Version].CurrentHierarchyMember,[Fix Version.By name].[Project]),       
         Descendants([Fix Version].CurrentHierarchyMember,[Fix version].CurrentHierarchy.Levels('Version'))
        )),  
      NOT isempty( [Fix Version].CurrentHierarchyMember.get('Start date'))), -- filter only those versions with Start Date
  [Fix Version].CurrentHierarchyMember.get('Start date'), BASC) -- order them to have the lowest date first
  .Item(0).get('Start date'), -- and get this start date
 Order( 
    Filter(
      Generate( -- Similar for getting the last release date
        { [Fix Version].CurrentHierarchyMember,
        ChildrenSet([Fix Version].CurrentHierarchyMember) },
        IIF([Fix Version].CurrentHierarchy.Name = "Fix Version.By name",
         Descendants([Fix Version].CurrentHierarchyMember,[Fix Version.By name].[Project]),       
         Descendants([Fix Version].CurrentHierarchyMember,[Fix version].CurrentHierarchy.Levels('Version'))
        )),  
      NOT isempty( [Fix Version].CurrentHierarchyMember.get('Release date'))), 
  [Fix Version].CurrentHierarchyMember.get('Release date'), BDESC)
  .Item(0).get('Release date'))
THEN 1
END

Lauma / support@eazybi.com

1 Like