Таблицы дат в Power Pivot необходимы для просмотра и вычисления данных с течением времени. Эта статья содержит подробное представление о таблицах дат и их создании в Power Pivot. В частности, в этой статье описаны:
-
Почему таблица дат важна для просмотра и вычисления данных по датам и времени.
-
Добавление таблицы дат в модель данных с помощью Power Pivot.
-
Создание новых столбцов дат, таких как Год, Месяц и Период в таблице дат.
-
Создание связей между таблицами дат и таблицами фактов.
-
Работа со временем.
Эта статья предназначена для пользователей, которые еще не были пользователями Power Pivot. Однако важно иметь хорошее представление об импорте данных, создании связей и создании вычисляемого столбца и мер.
В этой статье не описано, как использовать функции DAX Time-Intelligence в формулах мер. Дополнительные сведения о создании мер с помощью функций аналитики времени DAX см. в этой Excel.
Примечание: В Power Pivot имена "мера" и "вычисляемая поле" являются синонимами. Показатель имени используется в этой статье. Дополнительные сведения см. в теме Показатели в Power Pivot.
Содержание
Таблицы дат
Практически весь анализ данных включает просмотр и сравнение данных по датам и времени. Например, вам может потребоваться просуммировать объемы продаж за последний финансовый квартал, а затем сравнить эти итоги с другими кварталами или вычислить итоговую сумму закрытия месяца для счета. В каждом из этих случаев даты можно использовать для группировки и агрегированных операций продаж или остатков за определенный период времени.
Отчет Power View
Таблица дат может содержать множество различных представлений дат и времени. Например, таблица дат часто состоит из столбцов Финансовый год, Месяц, Квартал или Период, которые можно выбрать в качестве полей в списке полей при наличии и фильтрации данных в стеблях или отчетах Power View.
Список полей Power View
Для столбцов дат, таких как Год, Месяц и Квартал, чтобы включить все даты в соответствующий диапазон, таблица дат должна включать по крайней мере один столбец с набором дат. То есть этот столбец должен включать в таблицу дат по одной строке для каждого года.
Например, если в данных, которые вы хотите просмотреть, есть даты от 1 февраля 2010 г. до 30 ноября 2012 г. и вы составляете отчет по календарному году, вам нужна таблица дат с по крайней мере диапазоном дат от 1 января 2010 г. до 31 декабря 2012 г. Каждый год в таблице дат должен содержаться все дни для каждого года. Если вы будете регулярно обновлять данные с помощью новых данных, конечную дату можно обновить на один-два года, поэтому вам не нужно обновлять таблицу дат со временем.
Таблица дат с соединимым набором дат
При отчете по финансовому году можно создать таблицу дат с неумным набором дат для каждого финансового года. Например, если финансовый год начинается 1 марта и данные за финансовые годы с 2010 по текущую дату (например, в ФГ 2013) вы можете создать таблицу дат, которая начинается с 01.03.2009 и включает по крайней мере каждый день в каждый финансовый год до последней даты в 2013 финансовом году.
Если вы будете создавать отчеты как по календарному, так и по финансовому году, создавать отдельные таблицы дат не нужно. Одна таблица дат может включать столбцы для календарного года, финансового года и даже тринадцати четырехнедельного календаря. Важно то, что таблица дат содержит набор дат для всех включенных лет.
Добавление таблицы дат в модель данных
Добавить таблицу дат в модель данных можно несколькими способами:
-
Импорт из реляционной базы данных или другого источника данных.
-
Создайте таблицу дат в Excel а затем скопируйте новую таблицу в Power Pivot или создайте ссылку на нее.
-
Импорт из Microsoft Azure Marketplace.
Рассмотрим каждый из них более подробно.
Импорт из реляционной базы данных
При импорте части или всех данных из хранилища или другого типа реляционной базы данных, скорее всего, между ними уже есть таблица дат и связи между ними и остальными импортируемыми данными. Даты и формат, скорее всего, будут совпадать с датами в фактических данных, а даты, вероятно, хорошо начинаются с прошлого и уйдут далеко в будущее. Таблица дат, которые вы хотите импортировать, может быть очень большой и содержать диапазон дат, не включаемый в модель данных. С помощью расширенных функций мастера импорта таблиц Power Pivot можно выборочно выбрать только даты и конкретные столбцы, которые вам действительно нужны. Это может значительно уменьшить размер книги и повысить производительность.
Мастер импорта таблиц
В большинстве случаев создавать дополнительные столбцы, такие как Финансовый год, Неделя, Название месяца и т. д., не требуется, так как они уже существуют в импортируемой таблице. Однако в некоторых случаях после импорта таблицы дат в модель данных может потребоваться создать дополнительные столбцы дат в зависимости от конкретной необходимости создания отчетов. К счастью, это легко сделать с помощью DAX. Позже вы узнаете больше о создании полей таблицы дат. Каждая среда имеет разные среду. Если вы не уверены, есть ли у источников данных связанная дата или таблица календаря, поговорите с администратором базы данных.
Создание таблицы дат в Excel
Вы можете создать таблицу дат в Excel а затем скопировать ее в новую таблицу в модели данных. Это очень просто и обеспечивает гибкие возможности.
При создании таблицы дат в Excel начинается с одного столбца с неумным диапазоном дат. Затем можно создать дополнительные столбцы, такие как Год, Квартал, Месяц, Финансовый год, Период и т. д. на Excel с помощью формул Excel или после копирования таблицы в модель данных можно создать их в виде вычисляемого столбца. Дополнительные столбцы дат в Power Pivot описаны в разделе Добавление новых столбцов дат в таблицу дат далее в этой статье.
Как создать таблицу дат в Excel скопировать ее в модель данных
-
В Excel на пустом листе в ячейке A1введите имя столбца, чтобы определить диапазон дат. Как правило, это будет даты, даты и времени или DateKey.
-
В ячейке A2введите дату начала. Например, 01.01.2010.
-
Щелкните его и перетащите вниз на номер строки, который содержит конечную дату. Например, 31.12.2016.
-
Вы можете выбрать все строки в столбце Дата (включая имя в ячейке A1).
-
В группе Стили нажмите кнопку Форматированиетаблицы и выберите стиль.
-
В диалоговом окне Форматирование таблицы нажмите кнопку ОК.
-
Скопируйте все строки, включая заглавную.
-
В Power Pivot на вкладке Главная нажмите кнопку В таблицу.
-
В окне > введите имя таблицы, например "Дата" или "Календарь". Оставьте столбец Использовать первую строку в качествестолбцов, а затем нажмите кнопку ОК.
Новая таблица дат (в данном примере — Календарь) в Power Pivot выглядит так:
Примечание: Вы также можете создать связанную таблицу с помощью add to Data Model (Добавить в модель данных). Однако это делает книгу неоховеданной большой, так как книга имеет две версии таблицы дат. один в Excel и один в Power Pivot.
Примечание: Дата имени — это ключевое слово в Power Pivot. Если вы назовете таблицу, создаваемую в даты Power Pivot, ее имя необходимо будет заключено в одинарные кавычка в формулы DAX, которые ссылались на него в аргументе. Все примеры изображений и формул в этой статье относятся к таблице дат, созданной в Power Pivot с именем Календарь.
Теперь в модели данных есть таблица дат. С помощью DAX можно добавлять новые столбцы дат, такие как Year, Month и т. д.
Добавление новых столбцов дат в таблицу дат
Таблица дат с одним столбцом дат, который имеет по одной строке для каждого года, важна для определения всех дат в диапазоне дат. Это также необходимо для создания связи между таблицей фактов и таблицей дат. Однако этот столбец дат с одной строкой на каждый день не удобен при анализе дат в отчете pivotTable или Power View. Вы хотите, чтобы таблица дат включала столбцы, которые помогают агрегировать данные для диапазона или группы дат. Например, вы можете просуммируете объемы продаж по месяцам или кварталам или создать показатель, который вычисляет рост по годам. В каждом из этих случаев в таблице дат должны быть столбцы "Год", "Месяц" или "Квартал", которые позволяют агрегировать данные за этот период.
Если таблица дат импортирована из источника реляционных данных, возможно, она уже содержит нужные типы столбцов дат. В некоторых случаях может потребоваться изменить некоторые из этих столбцов или создать дополнительные столбцы дат. Это особенно актуально, если вы создаете собственную таблицу дат в Excel и копируете ее в модель данных. К счастью, создать столбцы дат в Power Pivot очень просто с помощью функций даты и времени в DAX.
Совет: Если вы еще не работали с DAX, начните обучение с помощью Краткогоstart: Обучение основам DAX за 30 минут на сайте Office.com.
Функции даты и времени DAX
Если вы когда-нибудь работали с функциями даты и времени в Excel, скорее всего, вы знакомы с функциями даты и времени. Хотя эти функции похожи на аналогичные в Excel, существуют некоторые важные различия.
-
Функции даты и времени DAX используют тип данных даты и времени.
-
Они могут принимать значения из столбца в качестве аргумента.
-
Их можно использовать для возврата и обработки значений дат.
Эти функции часто используются при создании пользовательских столбцов дат в таблице дат, поэтому их важно понимать. Мы будем использовать ряд этих функций для создания столбцов "Год", "Квартал", "Финансовыйmonth" и других.
Примечание: Функции даты и времени в DAX — это не то же самое, что и функции аналитики времени. Узнайте больше об аналитике времени в Power Pivot в Excel 2013.
DAX включает следующие функции даты и времени:
В формулах также можно использовать множество других функций DAX. Например, многие из описанных здесь формул используют математические и тригонометрические функции, такие как MOD и TRUNC,логические функции, такие как ЕСЛИ,и текстовые функции, такие как FORMAT. Дополнительные сведения о других функциях DAX см. в разделе Дополнительные ресурсы далее в этой статье.
Примеры формул для календарного года
В следующих примерах описаны формулы, используемые для создания дополнительных столбцов в таблице дат с именем Календарь. Один столбец с именем Дата уже существует и содержит диапазон дат от 01.01.2010 до 31.01.2016.
Год
=ГОД([дата])
В этой формуле функция ГОД возвращает год из значения в столбце Дата. Так как значение в столбце "Дата" имеет тип данных даты и времени, функция ГОД знает, как из него возвратить год.
Месяц
=МЕСЯЦ([дата])
В этой формуле, как и в случае с функцией ГОД, мы можем просто использовать функцию МЕСЯЦ для возврата значения месяца из столбца Дата.
Квартал
=INT(([Месяц]+2)/3)
В этой формуле функция INT используется для возврата значения даты в качестве integer. Аргумент, который мы указали для функции INT, — это значение из столбца Месяц, добавьте значение 2, а затем разделите его на 3, чтобы получить квартал от 1 до 4.
Название месяца
=FORMAT([дата];"мммм")
Чтобы получить название месяца в этой формуле, мы используем функцию FORMAT для преобразования числового значения из столбца "Дата" в текст. В качестве первого аргумента указывается столбец Дата, а затем формат; Мы хотим, чтобы в названии месяца были все символы, поэтому мы используем "мммм". Результат выглядит так:
Если мы хотим, чтобы название месяца, сокращенное до трех букв, в аргументе формат использовались буквы "ммм".
День недели
=FORMAT([дата];"дд")
В этой формуле мы используем функцию FORMAT, чтобы получить название дня. Так как мы хотим просто указать сокращенное название дня, в аргументе формат указывается "дд".
Пример сводной таблицы
После того как у вас есть поля для таких дат, как Год, Квартал, Месяц и т. д., вы можете использовать их в отчете или с помощью отчетов. Например, на рисунке ниже показано поле SalesAmount из таблицы Sales fact в valueS и Year and Quarter из таблицы измерений Календарь в строках. SalesAmount агрегируется в контексте года и квартала.
Примеры формул для финансового года
Финансовый год
=ЕСЛИ([Месяц]<= 6,[Год],[Год]+1)
В этом примере финансовый год начинается 1 июля.
Не существует функции, которая может извлечь финансовый год из значения даты, так как даты начала и окончания финансового года часто отличаются от дат календарного года. Чтобы получить финансовый год, сначала мы используем функцию ЕСЛИ для проверки, если значение месяца меньше или равно 6. Если значение аргумента Месяц меньше или равно 6, возвращается значение из столбца Год. Если нет, возвращается значение из года и добавляется значение 1.
Еще один способ указать значение для конца финансового года — создать меру, которая просто определяет месяц. Например, FYE:=6. Затем вы можете ссылаться на имя меры, а не на номер месяца. Например, =ЕСЛИ([Месяц]<=[ФГ],[Год],[Год]+1). Это обеспечивает большую гибкость при ссылке на финансовый месяц окончания года в нескольких различныхформулах.
Финансовый месяц
=ЕСЛИ([Месяц]<= 6, 6+[Месяц], [Месяц]- 6)
В этой формуле укажите, что значение для [Месяц] меньше или равно 6, а затем прибавьте значение 6 из значения "Месяц", в противном случае вычитайте 6 из значения [Месяц].
Финансовый квартал
=INT(([Финансовыйmonth]+2)/3)
Формула, используемая для fiscalQuarter, во значительной степени та же, что и для Квартала в календарном году. Единственное различие заключается в том, что мы укажите [FiscalMonth] вместо [Month].
Праздники и особые даты
Может потребоваться включить столбец даты, в который указывается, что некоторые даты — праздники или другая специальная дата. Например, вам может потребоваться свести итоги продаж для нового года, добавив поле "Праздники" в срез или фильтр в срез. В других случаях даты можно исключить из других столбцов дат или в мере.
Включив праздники или особые дни, достаточно просто. Вы можете создать в Excel таблицу с датами, которые вы хотите включить. Затем вы можете скопировать или использовать add to Data Model (Добавить в модель данных), чтобы добавить его в модель данных в виде связанной таблицы. В большинстве случаев создавать связь между таблицей и таблицей "Календарь" не требуется. Любые формулы, ссылаясь на нее, могут использовать функцию ПРОСМОТРПОЛЯ для возврата значений.
Ниже приведен пример таблицы, созданной в 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 |
Рождество |
В таблице дат мы создаем столбец "Праздники" и используем формулу такого типа:
=ПРОСМОТРПОЛЯ(Праздники[Праздники];Праздники[дата];Календарь[дата])
Рассмотрим эту формулу более тщательно.
Функция ПРОСМОТРПОЛЯ используется для получения значений из столбца Праздник в таблице "Праздники". В первом аргументе укажите столбец, в котором будет указано значение результата. В таблице "Праздники" указывается столбец Праздники, так как это значение, которое мы хотим вернуть.
=ПРОСМОТРПОЛЯ(Праздники[Праздники],Праздники[дата],Календарь[дата])
Затем укажите второй аргумент ( столбец поиска с датами, которые нужно найти). В таблице "Праздники" указывается столбец "Дата", например:
=ПРОСМОТРПОЛЯ(Праздники[Праздники];Праздники[дата],Календарь[дата])
Наконец, в таблице "Календарь" указан столбец с датами, которые нужно найти в таблице "Праздники". Это, конечно же, столбец "Дата" в таблице "Календарь".
=ПРОСМОТРПОЛЯ(Праздники[Праздники];Праздники[дата],Календарь[дата])
Столбец Праздники возвращает название праздников для каждой строки со значением даты, которое соответствует дате в таблице "Праздники".
Настраиваемый календарь : тринадцать четырехнедельных периодов
В некоторых организациях, например в розничной сети или в продуктах питания, часто приходится отчет о различных периодах, например о тринадцати четырехнедельных периодах. При календаре из тринадцати четырех недель каждый период составляет 28 дней; поэтому каждый период содержит четыре понедельника, четыре вторника, четыре среды и так далее. Каждый период содержит одинаковое количество дней, и, как правило, праздники попадают в один и тот же период каждого года. Вы можете начать период в любой день недели. Как и даты в календаре или финансовом году, с помощью DAX можно создавать дополнительные столбцы с настраиваемой датой.
В примерах ниже первый полный период начинается в первое воскресенье финансового года. В этом случае финансовый год начинается с 07.07.2010.
Неделя
Это значение дает номер недели, начиная с первой полной недели финансового года. В этом примере первая полная неделя начинается в воскресенье, поэтому первая полная неделя первого финансового года в таблице Календарь фактически начинается 04.07.2010 и продолжается до последней полной недели в таблице Календарь. Хотя само это значение не так полезно при анализе, необходимо вычислить его для использования в других формулах 28-дневного периода.
=INT([дата]-40356)/7)
Рассмотрим эту формулу более тщательно.
Сначала мы создадим формулу, которая возвращает значения из столбца "Дата" как значение типа "integer":
=INT([дата])
Затем мы хотим найти первое воскресенье первого финансового года. Мы видим, что это 04.07.2010.
Теперь вычитайте из этого значения значение 40356 (это число для 27.06.2010 г., последнее воскресенье предыдущего финансового года), чтобы получить количество дней с начала дней в таблице "Календарь", как по следующему:
=INT([дата]-40356)
После этого результат делится на 7 (дней в неделе), например:
=INT(([дата]-40356)/7)
Результат выглядит так:
Period
Период в этом настраиваемом календаре содержит 28 дней, и он всегда начинается в воскресенье. Этот столбец возвращает номер периода, который начинается с первого понедельника первого финансового года.
=INT(([Неделя]+3)/4)
Рассмотрим эту формулу более тщательно.
Во-первых, мы создадим формулу, которая возвращает значение из столбца Неделя в качестве значения типа integer, например:
=INT([Неделя])
Затем добавьте 3 к этому значению, например:
=INT([Неделя]+3)
После этого результат делится на 4, например:
=INT(([Неделя]+3)/4)
Результат выглядит так:
Период финансового года
Это значение возвращает финансовый год для периода.
=INT(([Период]+12)/13)+2008
Рассмотрим эту формулу более тщательно.
Сначала мы создадим формулу, которая возвращает значение из периода и добавляет 12:
= ([Период]+12)
Мы делим результат на 13, так как в финансовом году имеется тринадцать 28 дней:
=(([Период]+12)/13)
Мы добавляем 2010, так как это первый год в таблице:
=(([Период]+12)/13)+2010
Наконец, функция ЦЕЛОЕ используется для удаления любой части результата и возврата целого числа, разделенного на 13, например:
=INT(([Период]+12)/13)+2010
Результат выглядит так:
Период в финансовом году
Это значение возвращает номер периода от 1 до 13, начиная с первого полного периода (который начинается в воскресенье) каждого финансового года.
=ЕСЛИ(МОД([Период];13); МОД([Период];13);13)
Эта формула немного сложнее, поэтому сначала мы опишем ее на языке, который лучше понимать. В этой формуле говорится, что значение делится с [Период] на 13, чтобы получить номер периода (1–13) в году. Если это число 0, возвращается 13.
Сначала мы создадим формулу, которая возвращает остаток от значения "Период" на 13. Мы можем использовать функцию MOD (математические и тригонометрические функции), как пошагово:
=MOD([Период];13)
В большинстве случаев это дает нам нужный результат, за исключением тех случаев, когда значение для даты "Период" составляет 0, так как эти даты не попадают в первый финансовый год, как в первые пять дней примера таблицы дат календаря. Мы можем сделать это с помощью функции ЕСЛИ. Если результат — 0, возвращается 13, например:
=ЕСЛИ(MOD([Период];13),MOD([Период];13);13)
Результат выглядит так:
Пример сводной таблицы
На рисунке ниже показана таблица с полем SalesAmount из таблицы Sales fact table in VALUES и Поля PeriodFiscalYear и PeriodInFiscalYear из таблицы измерения "Дата календаря" в строках. SalesAmount агрегируется с учетом финансового года и 28-дневного периода финансового года.
Отношения
После создания таблицы дат в модели данных, чтобы приступить к просмотру данных в сводных таблицах и отчетах, а также агрегировать данные на основе столбцов в таблице измерений дат, необходимо создать связь между таблицей фактов с данными о транзакциях и таблицей дат.
Так как необходимо создать связь на основе дат, необходимо убедиться в том, что вы создали связь между столбцами, значения которых имеют тип данных даты и времени (дата).
Для каждого значения даты в таблице фактов связанный столбец подсмотра в таблице дат должен содержать совпадающие значения. Например, строка (запись транзакции) в таблице фактов "Продажи" со значением 15.08.2012 12:00 в столбце DateKey должна иметь соответствующее значение в связанном столбце "Дата" таблицы "Дата" (календарь). Это одна из самых важных причин, по которой столбец даты в таблице дат должен содержать не только диапазон дат, но и любую возможную дату.
Примечание: Хотя столбец даты в каждой таблице должен иметь один и тот же тип данных (Date), формат каждого столбца не имеет значения..
Примечание: Если Power Pivot не позволит создавать связи между двумя таблицами, поля дат могут не хранить дату и время с одинаковым уровнем точности. В зависимости от форматирования столбцов значения могут выглядеть одинаково, но храниться по-разному. Подробнее о работе со временем.
Примечание: Избегайте использования ключей замены в связях с другими людьми. При импорте данных из источника реляционных данных часто столбцы даты и времени представлены ключом замены, который представляет уникальную дату. В Power Pivot следует избегать создания связей с помощью ключей даты и времени с использованием ключей даты и времени, а вместо этого использовать столбцы, содержащие уникальные значения с типом данных "Дата". Несмотря на то, что использование ключей замены является лучшим решением в традиционных хранилищах данных, они не нужны в Power Pivot и могут затруднить группировку значений в скайтах по различным периодам дат.
Ошибка несоответствия типов при попытке создать связь, скорее всего, является тем, что столбец в таблице фактов не имеет типа данных "Дата". Это может произойти, если Power Pivot не может автоматически преобразовать нестандартную дату (обычно текстовый тип данных) в тип данных даты. Вы по-прежнему можете использовать столбец в таблице фактов, но вам придется преобразовать данные с помощью формулы DAX в новом вычисляемом столбце. См. далее в приложении преобразование дат текстового типа данных в тип данных даты.
Несколько связей
В некоторых случаях может потребоваться создать несколько связей или создать несколько таблиц дат. Например, если в таблице фактов "Продажи" есть несколько полей дат, таких как DateKey, ShipDate и ReturnDate, все они могут иметь связи с полем "Дата" в таблице "Дата" календаря, но только одно из них может быть активным. В этом случае dateKey представляет дату транзакции и, следовательно, наиболее важную дату, лучше всего будет служить активной связью. У других есть неактивные отношения.
Следующая pivottable вычисляет сумму продаж по финансовым годам и финансовым кварталам. Мера Total Sales с формулой Total Sales:=SUM([SalesAmount])помещается в значения, а поля FiscalYear и FiscalQuarter из таблицы даты календаря — в строки.
Эта прямая с помощью прямой стебли работает правильно, так как мы хотим суммировать общий объем продаж по дате транзакциив DateKey. Показатель Total Sales использует даты в DateKey и суммируется по финансовым годам и финансовым кварталам, так как в таблице "Продажи" есть связь между DateKey и столбцом Date в таблице дат календаря.
Неактивные связи
Но что делать, если нам нужно свести итоги продаж не по дате транзакции, а по дате отгрузки? Нам нужна связь между столбцом ShipDate в таблице Sales и столбцом Date в таблице Календарь. Если не создать эту связь, агрегаты будут всегда основаны на дате транзакции. Однако у нас может быть несколько связей, хотя только одна из них может быть активной, и так как дата транзакции является самой важной, она получает активную связь с таблицей Календарь.
В этом случае функция ShipDate имеет неактивное отношение, поэтому любая формула меры, созданная для агрегирования данных на основе дат отгрузки, должна указывать неактивное отношение с помощью функции USERELATIONSHIP.
Например, поскольку между столбцом ShipDate в таблице Sales и столбцом Date в таблице Календарь существует неактивная связь, мы можем создать меру, которая суммирует общий объем продаж по дате отгрузки. Для указания связи используется такая формула:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
В этой формуле просто говорится: вычисляется сумма для SalesAmount, но фильтруется с использованием связи между столбцом ShipDate в таблице Sales и столбцом Date в таблице Календарь.
Теперь, если мы создадим совокупную сумму продаж по дате отгрузки в области ЗНАЧЕНИЯ и финансовый год и финансовый квартал в строках, то вы увидите одинаковый общий итог, но все остальные суммы для финансового года и финансового квартала будут другими, так как они основаны на дате отгрузки, а не на дате транзакции.
Использование неактивных связей позволяет использовать только одну таблицу дат, но все меры (например, Total Sales by Ship Date) требуют ссылки на неактивное отношение в формуле. Кроме того, можно использовать несколько таблиц дат.
Несколько таблиц дат
Еще один способ работы с несколькими столбцами дат в таблице фактов — создать несколько таблиц дат и создать между ними отдельные активные отношения. Давайте еще раз посмотрим на пример таблицы "Продажи". У нас есть три столбца с датами, по которые может потребоваться агрегировать данные:
-
DateKey с датой продажи для каждой транзакции.
-
ShipDate с датой и временем, когда проданные товары были отправлены клиенту.
-
ReturnDate с датой и временем получения одного или более возвращенных элементов.
Помните, что поле DateKey с датой транзакции является наиболее важным. Мы будем делать большинство агрегатов на основе этих дат, поэтому, скорее всего, нам нужна связь между ней и столбцом "Дата" в таблице "Календарь". Если мы не хотим создавать неактивные отношения между полями ShipDate и ReturnDate и Date в таблице "Календарь", требуя формул специальных мер, можно создать дополнительные таблицы дат для дат отгрузки и даты возврата. Затем мы можем создать между ними активные отношения.
В этом примере мы создали еще одну таблицу дат с именем ShipCalendar. Это, конечно же, означает создание дополнительных столбцов дат, и поскольку эти столбцы дат находятся в другой таблице дат, мы хотим назвать их так, чтобы отличать их от тех же столбцов в таблице Календарь. Например, мы создали столбцы ShipYear, ShipMonth, ShipQuarter и так далее.
Если мы создадим ее и поставим показатель Total Sales в область ЗНАЧЕНИЯ, а shipFiscalYear и ShipFiscalQuarter — в строки, мы отберем те же результаты, что и при создании неактивной связи, и специальное вычисляемое поле Total Sales by Ship Date.
Каждый из этих подходов требует тщательного изучения. При использовании нескольких связей с одной таблицей дат может потребоваться создать специальные меры для перехода между неактивными отношениями с помощью функции USERELATIONSHIP. С другой стороны, создание нескольких таблиц дат может привести к путанице в списке полей, а так как в модели данных больше таблиц, для этого потребуется больше памяти. Поэкспериментируйте с наиболее оптимальными для вас вариантами.
Свойство «Таблица дат( Date Table)
Свойство Date Table задает метаданные, необходимые для правильной работы таких Time-Intelligence, как TOTALYTD, PREVIOUSMONTH и DATESBETWEEN. При вычислении с помощью одной из этих функций ячей формул Power Pivot знает, куда следует перейти, чтобы получить нужные даты.
Предупреждение: Если это свойство не заданной, меры, использующие Time-Intelligence DAX, могут возвращать некорректные результаты.
При этом указывается таблица дат и столбец даты с типом данных "Дата (дата и время)".
Как это сделать: настройка свойства Таблица дат
-
В окне PowerPivot выберите таблицу Календарь.
-
На вкладке Конструктор нажмите кнопку Пометить как таблицу дат.
-
В диалоговом окне Пометить как таблицу дат выберите столбец с уникальными значениями и типом данных "Дата".
Работа со временем
Все значения дат с типом данных "Дата" Excel или SQL Server фактически являются числом. В это число входят цифры, которые ссылаются на время. Во многих случаях время для каждой строки — полночь. Например, если поле DateTimeKey в таблице фактов "Продажи" имеет значения, такие как 19.10.2010 12:00:00, это означает, что значения имеют точность до уровня дня. Если значения поля DateTimeKey содержат время, например 19.10.2010 08:44:00, это означает, что значения имеют точность до уровня минут. Значения также могут быть до точности уровня "час" или даже с точностью в секундах. Точность значения времени существенно влияет на создание таблицы дат и связей между ней и таблицей фактов.
Необходимо определить, будут ли данные агрегироваться до ежедневного уровня точности или до точности времени. Другими словами, вы можете использовать столбцы в таблице дат, например "Утро", "После полудня" или "Час", в качестве полей даты времени в областях "Строка", "Столбец" или "Фильтр" в таблице.
Примечание: Дни — это наименьшая единица времени, с помощью которую могут работать функции аналитики времени DAX. Если вам не нужно работать со значениями времени, уменьшите точность данных, чтобы использовать дни в качестве минимальной единицы.
Если вы собираетесь агрегировать данные до уровня времени, в таблице дат должен быть столбец дат с включенным временем. На самом деле каждый год в диапазоне дат требуется столбец даты с одной строкой для каждого часа или даже каждую минуту каждого дня. Это необходимо для создания связи между столбцом DateTimeKey в таблице фактов и столбцом даты в таблице дат. Как можно представить, если вы укаплли много лет, это может привести к очень большой таблице дат.
Однако в большинстве случаев необходимо агрегировать данные только по дням. Другими словами, вы будете использовать такие столбцы, как Год, Месяц, Неделя или День недели в качестве полей в областях строк, столбцов или фильтров в pivottable. В этом случае столбец даты в таблице дат должен содержать только одну строку для каждого дня в году, как описано выше.
Если в столбце даты замерен уровень точности, но для создания связи между таблицей фактов и датой будет выровняться только дневный уровень, может потребоваться изменить таблицу фактов, создав новый столбец, в который будут усечены значения столбца дат до значения дня. Другими словами, преобразуем значение, например 19.10.2010, 08:44:00 в 19.10.2010, 12:00:00. Затем можно создать связь между этим новым столбцом и столбцом дат в таблице дат, так как значения совпадают.
Рассмотрим пример. На этом рисунке показан столбец DateTimeKey в таблице фактов "Продажи". Все агрегаты для данных в этой таблице должны быть только на уровне дня, используя столбцы в таблице даты календаря, такие как Год, Месяц, Квартал и т. д. Время, включаемая в значение, не относится к дате, а только к фактической дате.
Так как нам не нужно анализировать эти данные на уровне времени, столбец "Дата" в таблице дат календаря не должен включать по одной строке для каждого часа и каждой минуты каждого дня в каждом году. Таким образом, столбец "Дата" в таблице дат выглядит так:
Чтобы создать связь между столбцом DateTimeKey в таблице "Продажи" и "Дата" в таблице "Календарь", можно создать новый вычисляемый столбец в таблице "Факты о продажах" и использовать функцию ОТРСК для усечивания значения даты и времени в столбце DateTimeKey до значения даты, которое соответствует значениям в столбце "Дата" в таблице "Календарь". Наша формула выглядит так:
=ОТРСК([DateTimeKey];0)
При этом будет новый столбец (dateKey) с датой из столбца DateTimeKey и временем 12:00:00:00 для каждой строки:
Теперь мы можем создать связь между этим новым столбцом (DateKey) и столбцом Date в таблице Календарь.
Точно так же можно создать в таблице "Продажи" вычисляемый столбец, уменьшая точность времени в столбце DateTimeKey до уровня точности в часах. В этом случае функция ОТРЕКТ не будет работать, но мы по-прежнему можем использовать другие функции даты и времени DAX, чтобы извлечь и повторно свести новое значение до часовой точности. Мы можем использовать формулу вот так:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Новый столбец выглядит так:
Если столбец "Дата" в таблице дат имеет значения до уровня точности "час", мы можем создать связь между ними.
Обеспечение более утенимой даты
Многие столбцы дат, которые вы создаете в таблице дат, необходимы для других полей, но на самом деле не очень полезны для анализа. Например, поле DateKey в таблице Sales, на которую мы ссылались и которые были показаны в этой статье, важно, так как для каждой транзакции она регистрирована как происходят в определенную дату и время. Но с точки зрения анализа и отчетов это не совсем полезно, так как мы не можем использовать его в качестве поля строки, столбца или фильтра в таблице или отчете.
Точно так же в нашем примере столбец "Дата" в таблице "Календарь" очень удобен и критически важен на самом деле, но его нельзя использовать в качестве измерения в таблице.
Чтобы таблицы и столбцы в них были как можно полезнее, а также упростить навигацию по спискам полей отчета или таблицы Power View, важно скрыть ненужные столбцы в клиентских средствах. Кроме того, может потребоваться скрыть и некоторые таблицы. В таблице "Праздники", показанной ранее, содержатся даты праздников, важные для определенных столбцов таблицы "Календарь", но сами столбцы "Дата" и "Праздник" в таблице "Праздники" нельзя использовать в качестве полей в таблице. Чтобы упростить навигацию по спискам полей, можно скрыть всю таблицу "Праздники".
Еще один важный аспект работы с датами — это соглашения об именованиях. В Power Pivot можно назвать таблицы и столбцы любыми именами. Но помните, что, особенно если вы будете делиться книгой с другими пользователями, соглашение об именовывающих полях упрощает определение таблиц и дат не только в списках полей, но и в Power Pivot и формулах DAX.
После создания таблицы дат в модели данных можно приступить к созданию мер, которые помогут вам использовать все необходимые данные. Одни могут быть простыми, чем суммарные итоги продаж за текущий год, а другие — более сложными, где нужно отфильтровать конкретный диапазон уникальных дат. Дополнительные сведения о мерах в Power Pivot и функциях аналитики времени.
Приложение
Преобразование текстовых типов данных в тип данных "Дата"
В некоторых случаях таблица фактов с данными транзакций может содержать даты текстового типа данных. То есть дата, которая отображается как 2012-12-04T11:47:09, на самом деле не является датой или по крайней мере не может понять тип даты в Power Pivot. На самом деле это просто текст, который читается как дата. Чтобы создать связь между столбцом дат в таблице фактов и столбцом даты в таблице дат, оба столбца должны иметь тип данных "Дата".
Как правило, при попытке изменить тип данных для столбца дат, которые являются текстовыми типами данных, Power Pivot может автоматически интерпретировать даты и преобразовывать их в настоящий тип данных. Если Power Pivot не удается сделать преобразование типов данных, вы получите сообщение об ошибке несоответствия типов.
Однако вы все равно можете преобразовать даты в настоящий тип данных даты. Вы можете создать новый вычисляемый столбец и использовать формулу DAX для вычисления года, месяца, дня, времени и т. д. из текстовых строк, а затем свести их воедино таким образом, чтобы Power Pivot читал их как настоящую дату.
В этом примере мы импортировали в Power Pivot таблицу фактов "Продажи". Он содержит столбец "Дата и время". Значения выглядят так:
Если мы посмотрим на вкладку "Главная" группы "Тип данных" в группе Форматирование, то увидите, что это текстовый тип данных.
Мы не можем создать связь между столбцом DateTime и столбцом Date в таблице дат, так как типы данных не совпадают. При попытке изменить тип данных на Date(Дата) вы получаете сообщение об ошибке несоответствия типов:
В этом случае Power Pivot не удалось преобразовать тип данных из текста в дату. Мы по-прежнему можем использовать этот столбец, но чтобы привести его к действительному типу данных даты, нам нужно создать новый столбец, анализирует текст и повторно создает его в значение, которое Power Pivot может сделать типом данных "Дата".
Помните, что в разделе Работа со временем ранее в этой статье; Если для анализа не требуется точность суток, необходимо преобразовать даты в таблице фактов в дневные. Поэтому мы хотим, чтобы значения в новом столбце были на уровне дня точности (исключая время). Мы можем преобразовать значения в столбце DateTime в тип данных даты и удалить точность с помощью следующей формулы:
=ДАТА(ЛЕВЫЙ([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2))
Это дает новый столбец (в данном случае — Date). Power Pivot даже определяет значения, которые должны быть датами, и автоматически устанавливает для типа данных значение Date.
Если мы хотим сохранить уровень точности, мы просто расширяем формулу, включив часы, минуты и секунды.
=ДАТА(ЛЕВЫЙ([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2)) +
ВРЕМЯ(MID([DateTime];12;2), MID([DateTime];15;2), MID([DateTime];18;2))
Теперь, когда у нас есть столбец "Дата" с типом данных "Дата", мы можем создать связь между ним и столбцом даты в дате.
Дополнительные ресурсы
Краткое руководство. Обучение основам DAX за 30 минут
