Количество строк на основе их последнего значения в Excel PowerPivot с использованием DAX

Можно ли получить различное количество строк на основе самого последнего значения поля в таблице PowerPivot с помощью DAX?

У меня есть таблица транзакций, которая отслеживает специалистов с датами найма и перевода. Я хотел бы получить количество профессионалов на основе их последнего офиса. Таким образом, как профессиональные передачи через организацию, мы можем видеть, где они находятся в данный момент.

Таблица транзакций:

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

Желаемый результат:

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

Я создал меру, которая использует функцию DISTINCTCOUNT. Благодаря этому я могу правильно определить итоговую сумму, но итоги по отдельным офисам не такие, как хотелось бы. Я понимаю, что моя формула ниже делает то, что я прошу об этом. Тем не менее, я не уверен, как заставить его вернуть только офис профессионалов, основываясь на самой последней дате вступления в силу

DistinctCount:=DISTINCTCOUNT(TransactionTable[Name])

Вот вывод, используя меру DistinctCount, которую я создал

Я предполагаю, что мне придется использовать функцию CALCULATE и применить функцию FILTER, которая получит самые последние профессионалы в офисе, но я не уверен, как это будет выглядеть.

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

Кроме того, мы хотели бы, чтобы эти данные были поэтапно распределены по месяцам или кварталам. Это позволило бы нам увидеть кумулятивный подсчет каждого офиса с течением времени. У нас есть таблица дат, которая определяет месяцы, кварталы и годы.

Таблица дат:

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

Мне удалось выполнить поэтапный аспект этого, но подсчет не основывается на самом последнем офисе профессионала

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

Однако моя формула опять-таки не предназначена, чтобы получить только самые последние должности профессионалов. Однако итоговая сумма верна.

Ниже приведен вывод, который мы стремимся увидеть, но не можем получить.

Желаемый результат с использованием совокупных итогов за месяцы:

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
Источник

2 ответа

Решение

Вы можете сделать это, добавив еще одну совокупную сумму для всех новых офисных отъездов. Затем вычтите эту новую совокупную сумму из совокупной суммы для всех офисных столяров, которую вы уже рассчитали.

Сначала создайте новый вычисляемый столбец DepartureDate, чтобы записать дату ухода сотрудника из офиса. Для тех, кто никогда не переводил, просто оставьте это как сегодняшнюю дату, последнюю дату в вашей таблице Даты:

=
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] ))))

Затем создайте связь между этим вычисляемым столбцом и вашим полем Dates CalendarDate. Это будут неактивные отношения, поскольку у вас уже будет создано одно активное отношение.

Теперь создайте показатель для общего количества отправлений, ссылаясь на неактивные отношения:

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

Используя "<" вместо "<="Для пункта MAX Dates мы следим за тем, чтобы люди с сегодняшней датой не рассматривались как люди, которые уезжают сегодня.

Наконец, создайте еще одну новую меру, чтобы вычесть новую совокупную сумму из существующей:

Net:=[Cumulative DistinctCount]-[DeparturesCumulativeTotal]

Вот как это выглядит:

Источник

Привет Спасибо за этот пост огромную работу,

Пожалуйста, измените max как можно раньше, и distinctcount в Count или же Counta (зависит от типа данных):

Cumulative DistinctCount:=CALCULATE (
    Counta(TransactionTable[Name]),
    FILTER (
        ALL ( 'Dates'[CalendarDate] ),
        'Dates'[CalendarDate] <= Earliest (Dates[CalendarDate] )
    )
)
Источник
Другие вопросы по тегам