excelpowerpivotdaxbism

Count of Rows Based on Their Most Recent Value in Excel PowerPivot Using DAX

Is it possible to get a distinct count of rows based on a field's most recent value in a PowerPivot table using DAX?

I have a transactional table that tracks professionals with hire and transfer dates. I would like to get a count of the professionals based on their most recent office. This way as a profesional transfers through the organization we can see where they are at a given moment.

Transaction Table:

Name  | Action   | EffectiveDate | Office
-----------------------------------------
Peter | Hire     | 1/10/2014     | STL
John  | Hire     | 2/5/2014      | STL
John  | Transfer | 3/2/2014      | LAX
Jason | Hire     | 6/4/2014      | STL
John  | Transfer | 9/10/2014     | CHI

Desired Output:

Office | Distinct Count
-----------------------
CHI    | 1
STL    | 2
-----------------------
Total  | 3

I have created a measure that uses the DISTINCTCOUNT function. With that I am able to get the Grand Total correct, but the individual office totals are not as I would like. I understand that my formula below is doing what I am asking of it. However, I am not sure how to make it only return a professionals office based on the most recent Effective Date

DistinctCount:=DISTINCTCOUNT(TransactionTable[Name])

Here is the output using the DistinctCount measure that I have created enter image description here

I assume that I will have to use the CALCULATE function and apply a FILTER function that gets the distinct professionals most recent office, but I am not sure how that would look.

DistinctCountPerOffice:=CALCULATE (
    DISTINCTCOUNT(TransactionTable[Name]),
    FILTER (
        ?.....?
    )
)

Furthermore we would like this data time-phased over Months or Quarters. This would allow us to see each office cumulative count over time. We have a table of Dates that defines Months, Quarters, and Years.

Dates Table:

CalendarDate | Month Key | Month Name | Quarter Name | Year
-----------------------------------------------------------
1/1/2014     | 1.00      | Jan        | Q1           | 2014
1/2/2014     | 1.00      | Jan        | Q1           | 2014
...
2/1/2014     | 2.00      | Feb        | Q1           | 2014
....
8/1/2014     | 8.00      | Aug        | Q3           | 2014
..
9/2/2014     | 9.00      | Sep        | Q3           | 2014
..
12/16/2014   | 12.00     | Dec        | Q4           | 2014

I have been able to accomplish the time-phased aspect of this, but the counts are not based on on the professional's most recent office

Cumulative DistinctCount:=CALCULATE (
    DISTINCTCOUNT(TransactionTable[Name]),
    FILTER (
        ALL ( 'Dates'[CalendarDate] ),
        'Dates'[CalendarDate] <= MAX (Dates[CalendarDate] )
    )
)

However my formula again is not designed to only get the professionals most recent office. However the Grand Total is correct. enter image description here

Below is the output that we would are striving to see, but am not able to get.

Desired Ouput Using Cummulative Totals Over Months:

Month | CHI | LAX | STL
-----------------------
Jan   | 0   | 0   | 1 
Feb   | 0   | 0   | 2   
Mar   | 0   | 1   | 1
Apr   | 0   | 1   | 1
May   | 0   | 1   | 1
Jun   | 0   | 1   | 2
Jul   | 0   | 1   | 2
Aug   | 0   | 1   | 2
Sep   | 1   | 0   | 2
Oct   | 1   | 0   | 2
Nov   | 1   | 0   | 2
Dec   | 1   | 0   | 2
Solution

  • You can accomplish this by adding another cumulative total for all new office departures. Then subtract this new cumulative total from the cumulative total for all office joiners that you have already calculated.

    First create a new calculated column DepartureDate to record the date that the person departs the office. For people who have never transferred can just leave it as today's date, the last date in your Dates table:

    =
IF (
    ISBLANK (
        CALCULATE (
            MIN ( TransactionTable[Effectivedate] ),
            FILTER (
                TransactionTable,
                TransactionTable[Name] = EARLIER ( TransactionTable[Name] )
                    && TransactionTable[EffectiveDate] > EARLIER ( TransactionTable[EffectiveDate] )
    ))),
    MAX ( 'Dates'[CalendarDate] ),
    CALCULATE (
        MIN ( TransactionTable[Effectivedate] ),
        FILTER (
            TransactionTable,
            TransactionTable[Name] = EARLIER ( TransactionTable[Name] )
                && TransactionTable[EffectiveDate] > EARLIER ( TransactionTable[EffectiveDate] ))))

    Then create a relationship between this calculated column and your Dates CalendarDate field. This will be an inactive relationship since you will already have one active relationship created.

    Now create a measure for your departures cumulative total invoking the inactive relationship:

       DeparturesCumulativeTotal =
    CALCULATE (
        COUNTROWS ( TransactionTable ),
        USERELATIONSHIP ( TransactionTable[DepartureDate], 'Dates'[CalendarDate] ),
        FILTER (
            ALL ( 'Dates' ),
            'Dates'[CalendarDate] < MAX ( 'Dates'[CalendarDate] )
        )
    )

    By using "<" instead of "<=" for the MAX Dates clause we make sure that we don't see people with today's date as people who are departing today.

    Finally, create another new measure to subtract the new cumulative total from your existing one: 

    Net:=[Cumulative DistinctCount]-[DeparturesCumulativeTotal]

    This is what it looks like:

    enter image description here