PowerPivot Hierarchy Detection - Active Projects

Question

Building on Chris Campbell's Article regarding active projects, I'd like to add an extra dimension. My tables reference their own parent matter in a denormalised 2-layer hierarchy. After some calls to the RELATED() function, the main lookup table of projects can look like this:

My aim is to determine how many Parent groups were open in a given month. For example, at the end of January, there would be two (ABC Ltd and JQ public). The catch is that I also want to report on how many matters were closed in a period. Closure would only be counted when the last of the matters in a parent group is closed (7 Oct 2015 for ABC Ltd). So the output I would like is:

| Jan 15 | Aug 15 | Oct 15 Count Matters Open | 2 | 2 | 0 Count Opened | 2 | 0 | 0 Count Closed | 1 | 1 | 1
How can this be determined? Can I make each row aware of the status of others in its group?

Count Matters Open:=CALCULATE (
DistinctCount(Cases[ParentMatterName]),
FILTER (
    MatterListView,
    [MatterOpenAtEnd] = TRUE()   ),

Doesn't work because it will count each closure as well as each open matter.

greggyb greggyb · Accepted Answer · 2015-10-05T22:15:03

So, your verbal description doesn't seem to align with your output sample. The measures below work for your described needs, though, and much more generally.

Based on this requirement

Closure would only be counted when the last of the matters in a parent group is closed (7 Oct 2015 for ABC Ltd).

I have added two new fields to Cases as calculated columns to indicate whether a given row represents the first opened matter and one to represent the last closed matter in a parent group:

FirstOpened=
IF(
    [OpenDate] =
        CALCULATE(
            MIN('Cases'[OpenDate])
            ,ALLEXCEPT('Cases', 'Cases'[ParentMatterName])
        )
    ,1
    ,0
)

LastClosed:=
IF(
    [CloseDate] =
        CALCULATE(
            MAX('Cases'[CloseDate])
            ,ALLEXCEPT('Cases', 'Cases'[ParentMatterName])
        )
    ,1
    ,0
)

Then I've implemented the following measures:

CountMattersOpen:=
CALCULATE(
    DISTINCTCOUNT('Cases'[ParentMatterName])
    ,FILTER(
        VALUES('Cases'[OpenDate])
        ,'Cases'[OpenDate] <= MAX(DimDate[Date])
    )
    ,FILTER(
        VALUES('Cases'[CloseDate])
        ,'Cases'[CloseDate] >= MIN(DimDate[Date])
    )
)

CountOpened:=
CALCULATE(
    DISTINCTCOUNT('Cases'[ParentMatterName])
    ,FILTER(
        VALUES('Cases'[OpenDate])
        ,'Cases'[OpenDate] <= MAX(DimDate[Date])
            && 'Cases'[OpenDate] >= MIN(DimDate[Date])
    )
    ,'Cases'[FirstOpened] = 1
)


CountClosed:=
CALCULATE(
    DISTINCTCOUNT('Cases'[ParentMatterName])
    ,FILTER(
        VALUES('Cases'[CloseDate])
        ,'Cases'[CloseDate] <= MAX(DimDate[Date])
            && 'Cases'[CloseDate] >= MIN(DimDate[Date])
    )
    ,'Cases'[LastClosed] = 1
)

These work for any arbitrary (contiguous) set of dates, of which, calendar months represent a small subset. If you'd like you could select a time frame between any two arbitrary dates and the measures will still function appropriately. If you've put months onto the pivot table, then it will work just fine with the month boundaries of each pivot table row/column.

Note, this works only with the presence of a date dimension, and that date dimension must provide the rowfilters/columnfilters of the pivot table. Do not use any dates from Cases in the pivot rows/columns.

All we're doing is using FILTER() to step through the list of values making up Cases[CloseDate] and Cases[OpenDate] and filtering out those results that do not logically belong to a date context imposed by the pivot table (and respecting [FirstOpened]/[LastClosed] as necessary)

Again, I've followed your written descriptions, not your sample output.

My aim is to determine how many Parent groups were open in a given month.

[CountMattersOpen] will give you everything that was opened before the end of the month and closed after the beginning of the month (or any arbitrary time frame). This just counts everything open within the current date context

[CountOpened] doesn't have a specific definition, so I've made it the opposite of CountClosed - this counts the number of [ParentMatterName]s that have their first [OpenDate] in the current date context.

Closure would only be counted when the last of the matters in a parent group is closed (7 Oct 2015 for ABC Ltd).

[CountClosed] gives you the number of distinct [ParentMatterName]s that have the last sub-[MatterName] closed in the current date context.

Here is an image of my model diagram ([BeginningOfMonth] and [EndOfMonth] are unnecessary here):

PowerPivot Hierarchy Detection - Active Projects

2 Answers