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

Можно ли получить точное количество строк на основе самого последнего значения поля в PowerPivot table с использованием 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 введите описание изображения здесь

Я предполагаю, что мне придется использовать функцию РАСЧЕТ и применить Функция ФИЛЬТР, которая позволяет получить самые последние офисы отдельных профессионалов, но я не уверен, как это будет выглядеть.

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

excel powerpivot dax bism
person dotNetE    schedule 06.05.2015    source источник
comment
У меня был аналогичный набор данных, и я выполнил то, что вы ищете, создав таблицу моментальных снимков в SQL Server, а затем загрузив ее в Power PIvot. Вы определяете уровень детализации таблицы моментальных снимков (по сотрудникам, по офисам, по месяцам?). Затем вы можете просто сделать строку на человека на дату в таблице со значением численности персонала, равным 1. Думайте о численности персонала как о запасе. Эта статья может помочь: sqlbi.com/articles/   -  person mmarie    schedule 07.05.2015
comment
@mmarie Спасибо, возможно, нам придется пойти по этому пути. На этом этапе мы стараемся избегать выполнения ETL вне PowerPivot. Это будет первое знакомство бизнеса с этим набором данных, и мы знаем, что требования изменятся. Тем не менее, предоставленная вами статья очень информативна. Делая больше исследований. Я нашел эту статью: sqlblog.com/blogs/alberto_ferrari/archive/2011/02/09/, похоже, это соответствует тому, что я пытаюсь сделать, и обсуждаются концепции, аналогичные тому, что вы описали. Я собираюсь рассмотреть их более подробно.   -  person dotNetE    schedule 07.05.2015

Ответы (2)


arrow_upward
3
arrow_downward

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

Сначала создайте новый вычисляемый столбец 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]

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

введите описание изображения здесь

person Rory    schedule 10.05.2015
comment
Теперь я понимаю изменение, которое вы пытались внести в желаемый результат выше. Сделаю обновление. - person dotNetE; 11.05.2015

arrow_upward
-1
arrow_downward

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

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

Cumulative DistinctCount:=CALCULATE (
    Counta(TransactionTable[Name]),
    FILTER (
        ALL ( 'Dates'[CalendarDate] ),
        'Dates'[CalendarDate] <= Earliest (Dates[CalendarDate] )
    )
)
person user7581318    schedule 17.02.2017