Clever Geek Handbook

Number of rows based on their last value in Excel PowerPivot using DAX

Is it possible to get a distinct row count based on the last value of a field in a PowerPivot table using DAX ?

I have a transactional table that keeps track of professionals with rental and transfer dates. I would like to get a number of professionals based on their latest office. In this way, as a professional transfer through the organization, we can see where they are at the 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 this I can get the Grand Total correctly, but the individual office data is not as I would like. I understand that my formula below does what I ask for it. However, I am not sure how to do this, only returning the office of the professionals based on the most recent effective date.

DistinctCount:=DISTINCTCOUNT(TransactionTable[Name])

Here is the result using the DistinctCount dimension I created enter image description here

I guess I'll have to use the CALCULATE function and apply FILTER , which has been getting great pros lately, but I'm not sure how that would look.

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

In addition, we would like this data to be phased by month or quarter. This will allow us to see each office's cumulative invoice over time . We have a date table that defines months, quarters and years.

Date 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 was able to accomplish the phased aspect of this, but the count is not based on a professional last office

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

However, my formula is again not meant to get only the most recent specialist positions. However, Grand Total is correct. enter image description here

Below is the result that we would like to see, but cannot get.

Desired output using several months' totals:

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
+3


source to share


2 answers


You can accomplish this by adding another cumulative amount for all new office shipments. Then, subtract this new cumulative amount from the cumulative sum for all the mess halls that you have already calculated.

First, create a new calculated column, DepartureDate, to record the date the person leaves for the office. For people who have never transmitted, you can simply leave it as today's date, the latest date in the 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 the CalendarDate field. This will be an inactive relationship since you will already have one active relationship created.

Now, create a measure for your total outputs, causing the inactive ratio:

   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 proposal, we make sure we don't see people with today's date as people leaving today.

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

Net:=[Cumulative DistinctCount]-[DeparturesCumulativeTotal]

It looks like this:

enter image description here

+3


source


Hi, thanks for this post a great job,

Please change max

to the earliest and distinctcount

to Count

or Counta

(depends on the data type):



Cumulative DistinctCount:=CALCULATE (
    Counta(TransactionTable[Name]),
    FILTER (
        ALL ( 'Dates'[CalendarDate] ),
        'Dates'[CalendarDate] <= Earliest (Dates[CalendarDate] )
    )
)
-1


source






More articles:


All Articles