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

Question

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

I had a similar data set, and accomplished what you are looking for by making a snapshot table in SQL Server and then feeding that into Power PIvot. You determine the level of granularity of the snapshot table (by person by office, by month?). Then you can just make a row per person per date in the table with a headcount value of 1. Think of headcount as similar to inventory. This article might help: sqlbi.com/articles/… — mmarie
@mmarie Thanks, we may have to end up going that route. At this point we are trying to avoid doing ETL outside of PowerPivot. This will be the business' first exposure to this dataset, and we know requirements will change. Nonetheless, the article you provided is very informative. In doing more research. I found this article: sqlblog.com/blogs/alberto_ferrari/archive/2011/02/09/… it seems to in alignment with what I am trying to do and discusses similar concepts to what you have described. I am going to look at them in more detail. — dotNetE

Rory Rory · Accepted Answer · 2015-05-10T12:31:15

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

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

2 Answers