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

Таблицы дат в Power Pivot необходимы для просмотра и вычисления данных с течением времени. В этой статье содержатся подробные сведения о таблицах дат и способах их создания в Power Pivot. В частности, в этой статье описаны:

  • Почему таблица дат важна для просмотра и вычисления данных по датам и времени.

  • Использование Power Pivot для добавления таблицы дат в модель данных.

  • Создание столбцов даты, таких как Год, Месяц и Период, в таблице дат.

  • Создание связей между таблицами дат и таблицами фактов.

  • Как работать со временем.

Эта статья предназначена для пользователей, не знакомых с Power Pivot. Однако важно уже иметь хорошее представление об импорте данных, создании связей и создании вычисляемых столбцов и мер.

В этой статье не описывается использование функций DAX Time-Intelligence в формулах мер. Дополнительные сведения о создании мер с помощью функций аналитики времени DAX см. в разделе Аналитика времени в Power Pivot в Excel.

Примечание: В Power Pivot имена "measure" и "calculated field" являются синонимами. Мы используем меру имени в этой статье. Дополнительные сведения см. в разделе Меры в Power Pivot.

Содержание

Основные сведения о таблицах дат

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

Отчет Power View

Сводная таблица "Объем продаж за финансовый квартал"

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

Список полей Power View

Список полей Power View

Чтобы столбцы дат, такие как Год, Месяц и Квартал, включали все даты в соответствующем диапазоне, таблица дат должна содержать по крайней мере один столбец с непрерывным набором дат. То есть этот столбец должен содержать по одной строке на каждый день за каждый год, включенный в таблицу дат.

Например, если данные, которые вы хотите просмотреть, имеют даты с 1 февраля 2010 г. по 30 ноября 2012 г. и вы сообщаете о календарном году, то вам потребуется таблица дат с диапазоном дат как минимум с 1 января 2010 г. по 31 декабря 2012 г. Каждый год в таблице дат должен содержать все дни для каждого года. Если вы будете регулярно обновлять данные новыми данными, вам может потребоваться запустить конечную дату на год или два, чтобы вам не нужно обновлять таблицу дат с течением времени.

Таблица дат с непрерывным набором дат

Таблица данных со смежными датами

При отчете по финансовому году можно создать таблицу дат с непрерывным набором дат для каждого финансового года. Например, если ваш финансовый год начинается 1 марта и у вас есть данные за 2010 финансовый год до текущей даты (например, в 2013 финансовом году), можно создать таблицу дат, которая начинается 01.03.2009 и включает по крайней мере каждый день в каждом финансовом году до последней даты в 2013 финансовом году.

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

Добавление таблицы дат в модель данных

Можно добавить таблицу дат в модель данных несколькими способами:

  • Импорт из реляционной базы данных или другого источника данных.

  • Create таблицу дат в Excel, а затем скопируйте новую таблицу в Power Pivot или создайте ссылку на нее.

  • Импорт из Microsoft Azure Marketplace.

Давайте рассмотрим каждый из них более подробно.

Импорт из реляционной базы данных

Если вы импортируете некоторые или все данные из хранилища данных или другого типа реляционной базы данных, скорее всего, уже существует таблица дат и связи между ней и остальными импортируемыми данными. Даты и формат, скорее всего, будут совпадать с датами в ваших фактических данных, и даты, вероятно, начинаются хорошо в прошлом и выходят далеко в будущее. Таблица дат, которую требуется импортировать, может быть очень большой и содержать диапазон дат, превышающий то, что необходимо включить в модель данных. Вы можете использовать расширенные функции фильтрации мастера импорта таблиц Power Pivot, чтобы выборочно выбирать только даты и конкретные столбцы, которые вам действительно нужны. Это может значительно уменьшить размер книги и повысить производительность.

Мастер импорта таблиц

Диалоговое окно мастера импорта таблиц

В большинстве случаев вам не потребуется создавать дополнительные столбцы, такие как финансовый год, неделя, название месяца и т. д., так как они уже будут существовать в импортированной таблице. Однако в некоторых случаях после импорта таблицы дат в модель данных может потребоваться создать дополнительные столбцы дат в зависимости от конкретной потребности в отчетности. К счастью, это легко сделать с помощью DAX. Дополнительные сведения о создании полей таблицы дат вы узнаете позже. Каждая среда отличается. Если вы не уверены, есть ли у источников данных связанная дата или таблица календаря, обратитесь к администратору базы данных.

Create таблицу дат в Excel

Вы можете создать таблицу дат в Excel, а затем скопировать ее в новую таблицу в модели данных. Это действительно довольно легко сделать, и это дает вам много гибкости.

При создании таблицы дат в Excel вы начинаете с одного столбца с непрерывным диапазоном дат. Затем можно создать дополнительные столбцы, такие как Year, Quarter, Month, Fiscal Year, Period и т. д., на листе Excel с помощью формул Excel. После копирования таблицы в модель данных их можно создать в виде вычисляемых столбцов. Создание дополнительных столбцов даты в Power Pivot описано в разделе Добавление новых столбцов даты в таблицу дат далее в этой статье.

Практическое руководство. Create таблицу дат в Excel и ее копирование в модель данных

  1. В Excel на пустом листе в ячейке A1 введите имя заголовка столбца, чтобы определить диапазон дат. Как правило, это будетчто-то вроде Date, DateTime или DateKey.

  2. В ячейке A2 введите начальную дату. Например, 1/1/2010.

  3. Щелкните маркер заполнения и перетащите его вниз до номера строки с датой окончания. Например, 31.12.2016 г.

    Столбец "Дата" в Excel

  4. Выделите все строки в столбце Дата (включая имя заголовка в ячейке A1).

  5. В группе Стили щелкните Формат как таблица, а затем выберите стиль.

  6. В диалоговом окне Форматирование таблицы нажмите кнопку ОК.

    Столбец "Дата" в Power Pivot

  7. Скопируйте все строки, включая заголовок.

  8. В Power Pivot на вкладке Главная нажмите кнопку Вставить.

  9. В поле Вставка предварительного просмотра > имя таблицы введите имя , например Дата или Календарь. Установите флажок Использовать первую строку в качестве заголовков столбцови нажмите кнопку ОК.

    Просмотр вставки

    Новая таблица дат (в этом примере — Календарь) в Power Pivot выглядит следующим образом:

    Таблица дат в Power Pivot

    Примечание: Вы также можете создать связанную таблицу с помощью команды Добавить в модель данных. Однако это делает книгу неоправданно большой, так как в ней есть две версии таблицы дат. один в Excel и один в Power Pivot.

Примечание:  Дата имени является ключевое слово в Power Pivot. Если вы назовете таблицу, созданную в Power Pivot Date, необходимо заключить имя таблицы в одинарные кавычки во все формулы DAX, ссылающиеся на него в аргументе. Все примеры изображений и формул в этой статье относятся к таблице дат, созданной в Power Pivot с именем Calendar.

Теперь в модели данных есть таблица дат. Вы можете добавить новые столбцы даты, такие как Год, Месяц и т. д., с помощью DAX.

Добавление новых столбцов дат в таблицу дат

Таблица дат с одним столбцом дат, который содержит по одной строке для каждого дня для каждого года, важна для определения всех дат в диапазоне дат. Это также необходимо для создания связи между таблицей фактов и таблицей дат. Но один столбец дат с одной строкой на каждый день не полезен при анализе по датам в сводной таблице или отчете Power View. Вы хотите, чтобы таблица дат включала столбцы, которые помогают агрегировать данные для диапазона или группы дат. Например, может потребоваться суммировать объемы продаж по месяцам или кварталам или создать меру, которая вычисляет рост по сравнению с прошлым годом. В каждом из этих случаев для таблицы дат требуются столбцы year, month или quarter, которые позволяют агрегировать данные за этот период.

Если вы импортировали таблицу дат из реляционного источника данных, она может уже содержать нужные типы столбцов дат. В некоторых случаях может потребоваться изменить некоторые из этих столбцов или создать дополнительные столбцы дат. Это особенно верно, если вы создаете собственную таблицу дат в Excel и копируете ее в модель данных. К счастью, создание новых столбцов даты в Power Pivot довольно просто с помощью функций даты и времени в DAX.

Совет: Если вы еще не работали с DAX, отличное место для начала обучения — краткое руководство. Изучение основ DAX за 30 минут на Office.com.

Функции даты и времени DAX

Если вы когда-либо работали с функциями даты и времени в формулах Excel, то вы, скорее всего, знакомы с функциями даты и времени. Хотя эти функции аналогичны своим аналогам в Excel, существуют некоторые важные различия:

  • Функции даты и времени DAX используют тип данных datetime.

  • Они могут принимать значения из столбца в качестве аргумента.

  • Их можно использовать для возврата и (или) управления значениями даты.

Эти функции часто используются при создании настраиваемых столбцов дат в таблице дат, поэтому их важно понимать. Мы будем использовать ряд этих функций для создания столбцов для Year, Quarter, FiscalMonth и т. д.

Примечание: Функции даты и времени в DAX не совпадают с функциями аналитики времени. Дополнительные сведения об аналитике времени в Power Pivot в Excel 2013.

DAX включает следующие функции даты и времени:

В формулах также можно использовать множество других функций DAX. Например, во многих формулах, описанных здесь, используются математические и тригонометрические функции , такие как MOD и TRUNC, логические функции , например IF, и текстовые функции , такие как FORMAT . Дополнительные сведения о других функциях DAX см. в разделе Дополнительные ресурсы далее в этой статье.

Примеры формул для календарного года

В следующих примерах описываются формулы, используемые для создания дополнительных столбцов в таблице дат с именем Calendar. Один столбец с именем Дата уже существует и содержит непрерывный диапазон дат с 1/01/2010 по 31.12.2016.

Год

=YEAR([date])

В этой формуле функция YEAR возвращает год из значения в столбце Дата. Так как значение в столбце Дата имеет тип данных datetime, функция YEAR знает, как вернуть год из него.

Столбец "Год"

Месяцы

=MONTH([date])

В этой формуле, как и в случае с функцией YEAR, мы можем просто использовать функцию MONTH для возврата значения месяца из столбца Date.

Столбец "Месяц"

Квартал

=INT(([Месяц]+2)/3)

В этой формуле мы используем функцию INT для возврата значения даты в виде целого числа. Аргумент, который мы задаем для функции INT, — это значение из столбца Month, добавьте 2, а затем разделите его на 3, чтобы получить квартал, 1 к 4.

Столбец "Квартал"

Название месяца

=FORMAT([date];"mmmm")

В этой формуле, чтобы получить название месяца, мы используем функцию FORMAT для преобразования числового значения из столбца Date в текст. Мы указываем столбец Date в качестве первого аргумента, а затем формат; Мы хотим, чтобы в названии месяца отображались все символы, поэтому мы используем "mmmm". Результат выглядит так:

Столбец "Название месяца"

Если мы хотим вернуть название месяца, сокращенное до трех букв, в аргументе format используется слово mmm.

День недели

=FORMAT([date];"ddd")

В этой формуле мы используем функцию FORMAT, чтобы получить название дня. Так как нам нужно просто сокращенное название дня, мы указываем "ddd" в аргументе format.

Столбец "День недели"
Пример сводной таблицы

После создания полей для таких дат, как Год, Квартал, Месяц и т. д., их можно использовать в сводной таблице или отчете. Например, на следующем рисунке показано поле SalesAmount из таблицы Фактов продаж в разделе ЗНАЧЕНИЯ и Год и Квартал из таблицы измерения Календарь в строке. SalesAmount агрегируется по контексту года и квартала.

Пример сводной таблицы

Примеры формул для финансового года

Финансовый год

=ЕСЛИ([Месяц]<= 6,[Год],[Год]+1)

В этом примере финансовый год начинается 1 июля.

Не существует функции, которая могла бы извлечь финансовый год из значения даты, так как даты начала и окончания финансового года часто отличаются от дат календарного года. Чтобы получить финансовый год, мы сначала используем функцию IF , чтобы проверить, меньше или равно ли значение месяца 6. Во втором аргументе, если значение month меньше или равно 6, возвращается значение из столбца Year. Если нет, верните значение из Year и добавьте 1.

Столбец "Финансовый год"

Другой способ указать значение месяца окончания финансового года — создать меру, которая просто указывает месяц. Например, FYE:=6. Затем можно указать имя меры вместо номера месяца. Например, =IF([Month]<=[FYE],[Year],[Year]+1). Это обеспечивает большую гибкость при ссылке на конец финансового года в нескольких различных формулах.

Финансовый месяц

=ЕСЛИ([Месяц]<= 6, 6+[Месяц], [Месяц]- 6)

В этой формуле мы укажем, если значение для [Month] меньше или равно 6, затем возьмем значение 6 и добавим значение из Month, в противном случае вычитаем 6 из значения из [Month].

Столбец "Финансовый месяц"

Финансовый квартал

=INT(([FiscalMonth]+2)/3)

Формула, используемая для FiscalQuarter, во многом совпадает с формулой для квартала в нашем календарном году. Единственное различие заключается в том, что мы указываем [FiscalMonth] вместо [Month].

Столбец "Финансовый квартал"

Праздники или специальные даты

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

В том числе праздники или специальные дни довольно просто. В Excel можно создать таблицу с датами, которые нужно включить. Затем можно скопировать или с помощью команды Добавить в модель данных, чтобы добавить ее в модель данных в виде связанной таблицы. В большинстве случаев нет необходимости создавать связь между таблицей и таблицей Календарь. Любые формулы, ссылающиеся на него, могут использовать функцию LOOKUPVALUE для возврата значений.

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

Дата

Праздник

1/1/2010

Новый год

11/25/2010

День благодарения

12/25/2010

Рождество

01.01.2011

Новый год

11/24/2011

День благодарения

12/25/2011

Рождество

01.01.2012

Новый год

22.11.2012

День благодарения

12/25/2012

Рождество

1/1/2013

Новый год

11/28/2013

День благодарения

12/25/2013

Рождество

11/27/2014

День благодарения

12/25/2014

Рождество

01.01.2014

Новый год

11/27/2014

День благодарения

12/25/2014

Рождество

1/1/2015

Новый год

11/26/2014

День благодарения

12/25/2015

Рождество

01.01.2016

Новый год

11/24/2016

День благодарения

12/25/2016

Рождество

В таблице дат мы создадим столбец с именем Holiday и используем следующую формулу:

=LOOKUPVALUE(Праздники[Праздник];Праздники[дата];Календарь[дата])

Давайте рассмотрим эту формулу более внимательно.

Мы используем функцию LOOKUPVALUE для получения значений из столбца Holiday в таблице Holidays. В первом аргументе мы указываем столбец, в котором будет находиться наше результируемое значение. Мы указываем столбец "Праздники " в таблице "Праздники ", так как это значение, которое требуется вернуть.

=LOOKUPVALUE(Праздники[Праздник];Праздники[дата];Календарь[дата])

Затем мы указываем второй аргумент — столбец поиска с датами, которые требуется найти. Мы задаем столбец Дата в таблице Праздники следующим образом:

=LOOKUPVALUE(Праздники[Праздник];Праздники[дата];Календарь[дата])

Наконец, мы укажем столбец в таблице Календарь с датами, которые нужно найти в таблице Праздники . Это, конечно, столбец Дата в таблице Календарь .

=LOOKUPVALUE(Праздники[Праздник];Праздники[дата];Календарь[дата])

Столбец Праздник вернет имя праздника для каждой строки, которая имеет значение даты, соответствующее дате в таблице Holidays.

Таблица "Праздники"

Настраиваемый календарь — тринадцать четырехнедельных периодов

Некоторые организации, такие как розничная торговля или питание, часто сообщают о различных периодах, таких как тринадцать четырехнедельных периодов. При использовании тринадцати четырехнедельного календаря каждый период составляет 28 дней; таким образом, каждый период содержит четыре понедельника, четыре вторника, четыре среды и т. д. Каждый период содержит одинаковое количество дней, и, как правило, праздники будут приходится на один и тот же период каждый год. Вы можете начать период в любой день недели. Как и в случае с датами в календаре или финансовом году, можно использовать DAX для создания дополнительных столбцов с пользовательскими датами.

В приведенных ниже примерах первый полный период начинается с первого воскресенья финансового года. В этом случае финансовый год начинается с 7/1.

Неделя

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

=INT([date]-40356)/7)

Давайте рассмотрим эту формулу более внимательно.

Сначала мы создадим формулу, которая возвращает значения из столбца Date в виде целого числа, например:

=INT([date])

Затем мы хотим искать первое воскресенье в первом финансовом году. Мы видим, что это 04.07.2010.

Столбец "Неделя"

Теперь вычесть 40356 (это целое число для 27.06.2010, последнее воскресенье предыдущего финансового года) из этого значения, чтобы получить количество дней с начала дней в таблице календаря, например:

=INT([date]-40356)

Затем разделите результат на 7 (дней в неделю), как показано ниже:

=INT(([date]-40356)/7)

Результат выглядит следующим образом:

Столбец "Неделя"

Period

Период в этом пользовательском календаре содержит 28 дней, и он всегда начинается в воскресенье. Этот столбец возвращает число периода, начинающегося с первого воскресенья в первом финансовом году.

=INT(([Week]+3)/4)

Давайте рассмотрим эту формулу более внимательно.

Сначала мы создадим формулу, которая возвращает значение из столбца Week в виде целого числа, например:

=INT([Week])

Затем добавьте 3 к этому значению, как показано ниже:

=INT([Неделя]+3)

Затем разделите результат на 4, как показано ниже:

=INT(([Week]+3)/4)

Результат выглядит следующим образом:

Столбец "Период"

Финансовый год периода

Это значение возвращает финансовый год для периода.

=INT(([Period]+12)/13)+2008

Давайте рассмотрим эту формулу более внимательно.

Сначала мы создадим формулу, которая возвращает значение из Period и добавляет 12:

= ([Период]+12)

Мы делим результат на 13, так как в финансовом году существует тринадцать 28-дневных периодов:

=(([Период]+12)/13)

Мы добавим 2010 год, так как это первый год в таблице:

=(([Период]+12)/13)+2010

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

=INT(([Period]+12)/13)+2010

Результат выглядит следующим образом:

Столбец "Период финансовый год"

Период в финансовом году

Это значение возвращает число периода от 1 до 13, начиная с первого полного периода (начиная с воскресенья) в каждом финансовом году.

=IF(MOD([Period];13), MOD([Period];13),13)

Эта формула немного сложнее, поэтому мы сначала опишем ее на языке, который мы лучше понимаем. В этой формуле указано, что разделите значение из [Period] на 13, чтобы получить число периода (1–13) в году. Если это число равно 0, возвращается значение 13.

Во-первых, мы создадим формулу, которая возвращает оставшуюся часть значения от Точки на 13. Мы можем использовать MOD (математические и тригонометрические функции) следующим образом:

=MOD([Период],13)

Это, по большей части, дает нам нужный результат, за исключением тех случаев, когда значение периода равно 0, так как эти даты не попадают в первый финансовый год, как в первые пять дней в примере таблицы дат календаря. Мы можем позаботиться об этом с помощью функции IF. Если результат равен 0, мы возвращаем значение 13, как показано ниже.

=IF(MOD([Period];13);MOD([Period];13),13)

Результат выглядит следующим образом:

Столбец "Период в финансовом году"

Пример сводной таблицы

На рисунке ниже показана сводная таблица с полем SalesAmount из таблицы фактов продаж в разделе ЗНАЧЕНИЯ и поля PeriodFiscalYear и PeriodInFiscalYear из таблицы измерения даты календаря в строках. SalesAmount агрегируется для контекста по финансовому году и 28-дневному периоду финансового года.

Пример сводной таблицы за финансовый год

Отношения

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

Так как необходимо создать связь на основе дат, необходимо убедиться, что вы создаете эту связь между столбцами, значения которых относятся к типу данных datetime (Date).

Для каждого значения даты в таблице фактов связанный столбец подстановки в таблице дат должен содержать соответствующие значения. Например, строка (запись транзакции) в таблице фактов Sales со значением 15.08.2012 12:00 в столбце DateKey должна иметь соответствующее значение в соответствующем столбце Date в таблице date (Calendar). Это одна из наиболее важных причин, по которой столбец дат в таблице дат содержал непрерывный диапазон дат, включающий любую возможную дату в таблице фактов.

Создание связей в представлении схемы

Примечание: Хотя столбец даты в каждой таблице должен иметь один и тот же тип данных (Date), формат каждого столбца не имеет значения..

Примечание: Если Power Pivot не позволяет создавать связи между двумя таблицами, поля дат могут не хранить дату и время с одинаковым уровнем точности. В зависимости от форматирования столбцов значения могут выглядеть одинаково, но храниться по-разному. Дополнительные сведения о работе со временем.

Примечание: Избегайте использования целочисленных суррогатных ключей в отношениях. При импорте данных из реляционного источника часто столбцы даты и времени представляются суррогатным ключом, который представляет собой целочисленный столбец, используемый для представления уникальной даты. В Power Pivot следует избегать создания связей с использованием целочисленных ключей даты и времени, а вместо этого использовать столбцы, содержащие уникальные значения с типом данных date. Хотя использование суррогатных ключей считается рекомендуемым в традиционных хранилищах данных, целочисленные ключи не требуются в Power Pivot и могут затруднить группирование значений в сводных таблицах по разным периодам даты.

Если при попытке создать связь возникает ошибка несоответствия типов, скорее всего, это связано с тем, что столбец в таблице фактов не имеет типа данных Date. Это может произойти, если Power Pivot не может автоматически преобразовать тип данных, отличный от даты (обычно текстовый) в тип данных date. Вы по-прежнему можете использовать столбец в таблице фактов, но вам потребуется преобразовать данные с формулой DAX в новый вычисляемый столбец. См . раздел Преобразование дат текстового типа данных в тип данных даты далее в приложении.

Несколько связей

В некоторых случаях может потребоваться создать несколько связей или несколько таблиц дат. Например, если в таблице Sales имеется несколько полей даты, таких как DateKey, ShipDate и ReturnDate, все они могут иметь связи с полем даты в таблице дат календаря, но только одно из них может быть активным. В этом случае, так как DateKey представляет дату транзакции и, следовательно, самую важную дату, лучше всего использовать в качестве активной связи. Остальные имеют неактивные отношения.

В следующей сводной таблице вычисляется общий объем продаж по финансовому году и финансовому кварталу. Мера с именем Total Sales с формулой Total Sales:=SUM([SalesAmount])) помещается в значения, а поля FiscalYear и FiscalQuarter из таблицы дат календаря помещаются в строки.

Сводная таблица "Объем продаж за финансовый квартал" Список полей сводной таблицы

Эта прямая сводная таблица работает правильно, так как мы хотим суммировать общий объем продаж по дате транзакции в DateKey. Наша мера Total Sales использует даты в DateKey и суммируется по финансовому году и финансовому кварталу, так как существует связь между DateKey в таблице Sales и столбцом Date в таблице Дат календаря.

Неактивные связи

Но что делать, если мы хотим суммировать общий объем продаж не по дате транзакции, а по дате доставки? Нам нужна связь между столбцом ShipDate в таблице Sales и столбцом Date в таблице Calendar. Если мы не создадим эту связь, наши агрегаты всегда будут основаны на дате транзакции. Однако у нас может быть несколько связей, даже если только одна из них может быть активной, и так как дата транзакции является наиболее важной, она получает активную связь с таблицей Календарь.

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

Например, так как между столбцом ShipDate в таблице Sales и столбцом Date в таблице Календарь существует неактивная связь, можно создать меру, которая суммирует общий объем продаж по дате доставки. Мы используем следующую формулу, чтобы указать используемую связь:

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Эта формула просто гласит: вычислите сумму для SalesAmount, но выполните фильтрацию, используя связь между столбцом ShipDate в таблице Sales и столбцом Date в таблице Calendar.

Теперь, если мы создадим сводную таблицу и поместим меру Total Sales by Ship Date в значениях, а финансовый год и финансовый квартал в строках мы увидим один и тот же общий итог, но все остальные суммы за финансовый год и финансовый квартал отличаются, так как они основаны на дате отправки, а не дате транзакции.

Сводная таблица "Объем продаж по дате отгрузки" Список полей сводной таблицы

Использование неактивных связей позволяет использовать только одну таблицу дат, но для этого требуется, чтобы любые меры (например, Total Sales by Ship Date) ссылались на неактивную связь в ее формуле. Существует еще одна альтернатива, то есть использование нескольких таблиц дат.

Несколько таблиц дат

Другим способом работы с несколькими столбцами дат в таблице фактов является создание нескольких таблиц дат и создание отдельных активных связей между ними. Давайте еще раз рассмотрим пример таблицы Sales. У нас есть три столбца с датами, по которым может потребоваться агрегировать данные:

  • Объект DateKey с датой продажи для каждой транзакции.

  • ShipDate — с датой и временем отправки проданных товаров клиенту.

  • ReturnDate — с датой и временем получения одного или нескольких возвращенных элементов.

Помните, что наиболее важным является поле DateKey с датой транзакции. Мы будем выполнять большую часть агрегатов на основе этих дат, поэтому нам, безусловно, потребуется связь между ним и столбцом Date в таблице Календарь. Если мы не хотим создавать неактивные связи между ShipDate и ReturnDate и полем Date в таблице Календарь, что требует специальных формул мер, можно создать дополнительные таблицы дат для даты отправки и даты возврата. Затем можно создать активные связи между ними.

Связи с таблицами с несколькими записями дат в представлении схемы

В этом примере мы создали другую таблицу дат с именем ShipCalendar. Это, конечно, также означает создание дополнительных столбцов дат, и поскольку эти столбцы дат находятся в другой таблице дат, мы хотим назвать их таким образом, чтобы отличать их от одних и того же столбца в таблице Календарь. Например, мы создали столбцы с именами ShipYear, ShipMonth, ShipQuarter и т. д.

Если мы создадим сводную таблицу и поместим нашу меру Total Sales в значения, а ShipFiscalYear и ShipFiscalQuarter — в ROWS, мы увидим те же результаты, что и при создании неактивной связи и специального вычисляемого поля Total Sales by Ship Date.

Сводная таблица "Объем продаж по дате отгрузки" с календарем отгрузки Список полей сводной таблицы

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

Свойство Date Table

Свойство Таблица дат задает метаданные, необходимые для правильной работы Time-Intelligence функций, таких как TOTALYTD, PREVIOUSMONTH и DATESBETWEEN. Когда вычисление выполняется с помощью одной из этих функций, подсистема формул Power Pivot знает, куда идти, чтобы получить необходимые даты.

Предупреждение: Если это свойство не задано, меры, использующие функции DAX Time-Intelligence, могут не возвращать правильные результаты.

При задании свойства Таблица дат в ней указываются таблица дат и столбец дат типа данных Date (datetime).

Диалоговое окно "Пометка как таблицы дат"

Практическое руководство. Установка свойства Date Table

  1. В окне PowerPivot выберите таблицу Календарь .

  2. На вкладке Конструктор щелкните Пометить как таблицу дат.

  3. В диалоговом окне Пометить как таблицу дат выберите столбец с уникальными значениями и типом данных Date.

Работа со временем

Все значения дат с типом данных Date в Excel или SQL Server фактически являются числами. В это число входят цифры, которые относятся к времени. Во многих случаях это время для каждой строки равно полуночи. Например, если поле DateTimeKey в таблице фактов sales содержит такие значения, как 19.10.2010 12:00:00 AM, это означает, что значения относятся к уровню точности дня. Если значения поля DateTimeKey включают время, например 19.10.2010 8:44:00 AM, это означает, что значения имеют точность в минуту. Значения также могут быть точностью на уровне часов или даже в секундах. Уровень точности значения времени будет оказывать значительное влияние на способ создания таблицы дат и связи между ней и таблицей фактов.

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

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

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

Однако в большинстве случаев требуется агрегировать данные только в течение дня. Другими словами, вы будете использовать такие столбцы, как Год, Месяц, Неделя или День недели в качестве полей в областях строки, столбца или фильтра сводной таблицы. В этом случае столбец дат в таблице дат должен содержать только одну строку для каждого дня в году, как мы уже говорили ранее.

Если столбец дат содержит уровень точности времени, но вы будете агрегировать только до уровня дня, чтобы создать связь между таблицей фактов и таблицей дат, может потребоваться изменить таблицу фактов, создав новый столбец, который усекает значения в столбце даты до значения дня. Другими словами, преобразуйте значение 19.10.2010 8:44:00в19.10.2010 12:00:00. Затем можно создать связь между этим новым столбцом и столбцом даты в таблице дат, так как значения совпадают.

Рассмотрим пример. На этом изображении показан столбец DateTimeKey в таблице фактов Sales. Все агрегаты для данных в этой таблице должны быть только на уровне дня с использованием столбцов в таблице календарных дат, таких как Year, Month, Quarter и т. д. Время, включенное в значение, не имеет значения, только фактическая дата.

Столбец "КлючДатыИВремени"

Так как нам не нужно анализировать эти данные на уровне времени, столбец Дата в таблице даты календаря не должен включать одну строку для каждого часа и каждую минуту каждого дня в каждом году. Таким образом, столбец Дата в таблице дат выглядит следующим образом:

Столбец "Дата" в Power Pivot

Чтобы создать связь между столбцом DateTimeKey в таблице Sales и столбцом Date в таблице Календарь, можно создать новый вычисляемый столбец в таблице Факты продаж и использовать функцию TRUNC для усечения значения даты и времени в столбце DateTimeKey в значение даты, соответствующее значениям в столбце Date в таблице Calendar. Наша формула выглядит следующим образом:

=TRUNC([DateTimeKey],0)

Это дает нам новый столбец (мы назвали DateKey) с датой из столбца DateTimeKey и временем 12:00:00 AM для каждой строки:

Столбец "КлючДаты"

Теперь можно создать связь между этим новым столбцом (DateKey) и столбцом Date в таблице Calendar.

Аналогичным образом можно создать вычисляемый столбец в таблице Sales, который уменьшает точность времени в столбце DateTimeKey до часового уровня точности. В этом случае функция TRUNC не будет работать, но мы по-прежнему можем использовать другие функции DAX даты и времени для извлечения и повторного объединения нового значения до часового уровня точности. Мы можем использовать следующую формулу:

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

Наш новый столбец выглядит следующим образом:

Столбец "КлючДатыИВремени"

Если столбец Date в таблице дат содержит значения на уровне точности часов, мы можем создать связь между ними.

Сделать даты более пригодными для использования

Многие столбцы дат, создаваемые в таблице дат, необходимы для других полей, но на самом деле не так полезны для анализа. Например, поле DateKey в таблице Sales, на которую мы ссылались и отображались в этой статье, важно, так как для каждой транзакции эта транзакция записывается как происходящая в определенную дату и время. Но с точки зрения анализа и отчетности это не так полезно, так как мы не можем использовать его в качестве поля строки, столбца или фильтра в сводной таблице или отчете.

Аналогичным образом, в нашем примере столбец Дата в таблице Календарь очень полезен, на самом деле критически важный, но его нельзя использовать в качестве измерения в сводной таблице.

Чтобы таблицы и столбцы в них были максимально полезными, а также упростить навигацию по спискам полей сводной таблицы или отчета Power View, важно скрыть ненужные столбцы в клиентских средствах. Вы также можете скрыть некоторые таблицы. Показанная ранее таблица "Праздники" содержит даты праздников, важные для определенных столбцов таблицы "Календарь", но столбцы "Дата" и "Праздник" в таблице "Праздники" нельзя использовать в качестве полей в сводной таблице. Чтобы упростить навигацию по Списки Field, можно скрыть всю таблицу Праздники.

Еще одним важным аспектом работы с датами являются соглашения об именовании. Таблицы и столбцы в Power Pivot можно присвоить любым нужным именам. Но имейте в виду, что, особенно если вы будете совместно использовать книгу с другими пользователями, хорошее соглашение об именовании упрощает определение таблиц и дат не только в полях Списки, но и в Power Pivot и в формулах DAX.

После создания таблицы дат в модели данных можно приступить к созданию мер, которые помогут вам максимально эффективно использовать данные. Некоторые могут быть столь же простыми, как суммирование итогов продаж за текущий год, а другие могут быть более сложными, где необходимо фильтровать по определенному диапазону уникальных дат. Дополнительные сведения см. в статье Меры в Power Pivot и функции аналитики времени.

Приложение

Преобразование дат текстового типа данных в тип данных даты

В некоторых случаях таблица фактов с данными транзакций может содержать даты текстового типа данных. То есть дата, которая отображается как 2012-12-04T11:47:09, на самом деле не является датой вообще или, по крайней мере, тип даты, которую может понять Power Pivot. Это действительно просто текст, который читается как дата. Чтобы создать связь между столбцом дат в таблице фактов и столбцом дат в таблице дат, оба столбца должны иметь тип данных Date .

Как правило, при попытке изменить тип данных для столбца дат, которые являются текстовым типом данных, на тип данных date, Power Pivot может автоматически интерпретировать даты и преобразовать их в тип данных true date. Если Power Pivot не может преобразовать тип данных, вы получите ошибку несоответствия типов.

Однако вы по-прежнему можете преобразовать даты в тип данных true date. Вы можете создать новый вычисляемый столбец и использовать формулу DAX для синтаксического анализа года, месяца, дня, времени и т. д. из текстовых строк, а затем сцепить их обратно, чтобы Power Pivot считывал как истинную дату.

В этом примере мы импортировали таблицу фактов с именем Sales в Power Pivot. Он содержит столбец с именем DateTime. Значения выглядят следующим образом:

Столбец "Дата и время" в таблице фактов.

Если мы рассмотрим тип данных на вкладке Главная страница группы форматирования PowerPivot, то увидим, что это тип данных Text.

Тип данных на ленте

Невозможно создать связь между столбцом DateTime и столбцом Date в таблице дат, так как типы данных не совпадают. При попытке изменить тип данных на Date мы получим ошибку несоответствия типов:

Ошибка несоответствия

В этом случае Power Pivot не удалось преобразовать тип данных из текста в дату. Мы по-прежнему можем использовать этот столбец, но чтобы получить его в тип данных true date, необходимо создать новый столбец, который анализирует текст и повторно создает его в значение, которое Power Pivot может сделать типом данных Date.

Помните, что из раздела Работа с временем ранее в этой статье; Если анализ не требует точности по времени суток, даты в таблице фактов следует преобразовывать в уровень точности дня. Учитывая это, мы хотим, чтобы значения в новом столбце были на уровне точности дня (за исключением времени). Мы можем преобразовать значения в столбце DateTime в тип данных date и удалить уровень точности времени с помощью следующей формулы:

=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2))

Мы получаем новый столбец (в данном случае — Date). Power Pivot даже определяет значения, которые должны быть датами, и автоматически задает тип данных Date.

Столбец "Дата" в таблице фактов

Если мы хотим сохранить уровень точности времени, мы просто расширим формулу, включив в нее часы, минуты и секунды.

=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2)) +

TIME(MID([DateTime];12;2), MID([DateTime];15;2), MID([DateTime];18;2))

Теперь, когда у нас есть столбец Date типа данных Date, можно создать связь между ним и столбцом даты в дате.

Дополнительные ресурсы

Даты в PowerPivot

Вычисления в Power Pivot

Краткое руководство. Обучение основам DAX за 30 минут

Справочник по выражениям анализа данных

Центр ресурсов DAX

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?
После нажатия кнопки "Отправить" ваш отзыв будет использован для улучшения продуктов и служб Майкрософт. Эти данные будут доступны для сбора ИТ-администратору. Заявление о конфиденциальности.

Спасибо за ваш отзыв!

×