Таблицы дат в Power Pivot необходимы для просмотра и вычисления данных с течением времени. В этой статье приводится подробное представление о таблицах дат и их создании в Power Pivot. В частности, в этой статье описывается:
- Почему таблица дат важна для просмотра и вычисления данных по дате и времени.
- Добавление таблицы дат в модель данных с помощью Power Pivot.
- Как создавать в таблице даты новые столбцы дат, такие как "Год", "Месяц" и "Период".
- Создание связей между таблицами дат и таблицами фактов.
- Как работать со временем.
Эта статья предназначена для пользователей, не имеющих опыта работы с Power Pivot. Однако важно уже сейчас хорошо разбираться в импорте данных, создании связей и создании вычисляемых столбцов и мер.
В этой статье не описывается использование функций DAX Time-Intelligence в формулах меры. Дополнительные сведения о создании измерений с помощью функций DAX Time Intelligence см. в статье Аналитика времени в Power Pivot в 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 введите имя заголовка столбца, чтобы определить диапазон дат. Как правило, это Date, DateTime или DateKey.
В ячейке A2 введите дату начала. Например, 01.01.2010.
Щелкните маркер заполнения и перетащите его вниз к номеру строки, содержащей дату окончания. Например, 31.12.2016.
Выделите все строки в столбце "Дата " (включая имя заголовка в ячейке A1).
В группе "Стили " щелкните "Форматировать как таблицу" и выберите стиль.
В диалоговом окне Форматирование таблицы нажмите кнопку ОК.
Копировать все строки, включая заголовок.
В Power Pivot на вкладке "Главная " щелкните "Вставить".
В поле "Вставить>имя таблицы" введите имя, например "Дата" или "Calendar". Оставьте флажок "Использовать первую строку в качестве заголовков столбцов"и нажмите кнопку "ОК".
Новая таблица дат (в этом примере — Calendar) в Power Pivot выглядит следующим образом:
Примечание
Вы также можете создать связанную таблицу с помощью команды "Добавить в модель данных". Однако это делает вашу книгу излишне большой, так как в ней есть две версии таблицы дат. один в Excel и один в Power Pivot.
Примечание
Имя дата — это ключевое слово в Power Pivot. Если вы присвоите имя таблице, созданной в Power Pivot Date, вам потребуется заключить ее в одинарные кавычки во всех формулах DAX, ссылающихся на нее в качестве аргумента. Все примеры изображений и формул в этой статье относятся к таблице дат, созданной в Power Pivot с именем Calendar.
В модели данных появилась таблица дат. Вы можете добавлять новые столбцы даты, такие как год, месяц и т. д., используя DAX.
Добавление новых столбцов даты в таблицу дат
Таблица дат с одним столбцом дат, содержащей по одной строке на каждый день каждого года, важна для определения всех дат в диапазоне дат. Она также необходима для создания связи между таблицей фактов и таблицей дат. Но один столбец дат с одной строкой для каждого дня бесполезен при анализе по датам в отчете сводной таблицы или Power View. Вы хотите, чтобы таблица дат содержала столбцы, помогающие агрегировать данные для диапазона или группы дат. Например, можно суммировать объем продаж по месяцам или кварталам или создать меру, вычисляющую рост по годам. В каждом из этих случаев в таблицу дат входят столбцы года, месяца или квартала, позволяющие агрегировать данные за этот период.
Если вы импортировали таблицу дат из реляционного источника, возможно, она уже включает нужные вам типы столбцов дат. В некоторых случаях может потребоваться изменить некоторые из этих столбцов или создать дополнительные столбцы дат. Это, в частности, касается создания в Excel таблицы дат и ее копирования в модель данных. К счастью, создавать новые столбцы дат в Power Pivot довольно легко с помощью функций даты и времени в DAX.
Совет
Если вы еще не работали с DAX, то начать обучение можно с краткого руководства: Изучите основы DAX за 30 минут на Office.com.
Функции даты и времени DAX
Если вы когда-либо работали с функциями даты и времени в формулах Excel, то, вероятно, знакомы с функциями даты и времени. Хотя эти функции похожи на аналогичные аналогичные функции в Excel, есть ряд важных отличий:
- В функциях даты и времени DAX используется тип данных datetime.
- Они могут принимать значения из столбца в качестве аргумента.
- Их можно использовать для возврата значений дат и (или) изменения их значений.
Эти функции часто используются при создании настраиваемых столбцов дат в таблице дат, поэтому их важно понимать. Мы воспользуемся некоторыми из этих функций для создания столбцов для Year, Quarter, FiscalMonth и т. д.
Примечание
Функции даты и времени в DAX отличаются от функций логики ко времени. Дополнительные сведения о функции операций по анализу времени в Power Pivot в Excel.
В DAX есть следующие функции даты и времени:
- ДАТА
- ДАТАЗНАЧ
- НА СЛЕДУЮЩИЙ ДЕНЬ
- ДАТАМЕС
- КОНМЕСЯЦА
- HOUR
- MINUTE
- MONTH
- ТДАТА
- SECOND
- TIME
- ВРЕМЗНАЧ
- СЕГОДНЯ
- ДЕНЬНЕД
- НОМНЕДЕЛИ
- YEAR
- ДОЛЯГОДА
В формулах можно использовать множество других функций DAX. Например, во многих описанных здесь формулах используются математические и тригонометрические функции (ОСТАТ и ОТБР),логические функции (например, ЕСЛИ) и текстовые функции (ФОРМАТ). Дополнительные сведения о других функциях DAX см. в разделе "Дополнительные ресурсы" далее в этой статье.
Примеры формул для календарного года
В следующих примерах описаны формулы, используемые для создания дополнительных столбцов в таблице дат с именем Calendar. Один столбец с именем "Дата" уже существует и содержит непрерывный диапазон дат от 01.01.2010 до 31.12.2016.
Год
=ГОД([дата])
В этой формуле функция ГОД возвращает год из значения в столбце "Дата". Так как значение в столбце "Дата" имеет тип данных datetime, функция ГОД знает, как получить год из него.
Месяцы
=МЕСЯЦ([дата])
В этой формуле, как и в случае с функцией ГОД, можно просто использовать функцию МЕСЯЦ для возврата значения месяца из столбца "Дата".
Квартал
=ЦЕЛОЕ(([месяц]+2)/3)
В этой формуле функция ЦЕЛОЕ используется для возврата целого значения даты. Аргумент, который мы указываем для функции INT, — это значение из столбца Month, добавьте 2, а затем разделите это на 3, чтобы получить наш квартал, от 1 до 4.
Название месяца
=FORMAT([date];"mmmm")
В этой формуле для получения названия месяца мы используем функцию FORMAT , чтобы преобразовать числовое значение из столбца "Дата" в текст. В качестве первого аргумента указываем столбец Дата, а затем формат; Мы хотим, чтобы название месяца отображало все символы, поэтому мы используем "мммм". Результат выглядит так:
Если мы хотим вернуть название месяца, сокращенное до трех букв, мы будем использовать "mmm" в аргументе format.
День недели
=FORMAT([date];"ddd")
В этой формуле мы используем функцию FORMAT для получения названия дня. Так как нам нужно только сокращенное название дня, мы указываем "ddd" в аргументе format.
Пример сводной таблицы
Если у вас есть поля для дат, таких как год, квартал, месяц и т. д., их можно использовать в сводной таблице или отчете. Например, на следующем рисунке показано поле SalesAmount из таблицы фактов продаж в VALUES и поле Year и Quarter из таблицы измерений Calendar в ROWS. Значение SalesAmount агрегируется для контекста года и квартала.
Примеры формул для финансового года
Финансовый год
=ЕСЛИ([Месяц]<= 6;[Год];[Год]+1)
В этом примере финансовый год начинается 1 июля.
Не существует функции, которая могла бы извлечь финансовый год из значения даты, поскольку даты начала и окончания финансового года часто отличаются от дат календарного года. Чтобы получить финансовый год, сначала с помощью функции ЕСЛИ проверяем, не меньше ли значение Month и не равно 6. Во втором аргументе, если значение аргумента "Месяц" не больше 6, возвращается значение из столбца "Год". Если нет, верните значение из Year и добавьте 1.
Еще один способ указать значение месяца окончания финансового года — создать меру, которая просто указывает месяц. Например, FYE:=6. Затем можно указать имя меры вместо номера месяца. Например, =ЕСЛИ([Месяц]<=[ГОД];[Год];[Год]+1). Это обеспечивает большую гибкость при ссылке на месяц окончания финансового года в нескольких разных формулах.
Финансовый месяц
=ЕСЛИ([Месяц]<= 6; 6+[Месяц]; [Месяц]- 6)
В этой формуле указывается, что значение для [Месяц] меньше или равно 6, возьмите 6 и добавьте значение из значения Месяц, в противном случае вычтите 6 из значения [Месяц].
Финансовый квартал
=ЦЕЛОЕ(([Месяц финансового года]+2)/3)
Формула, используемая для финансового квартала, во многом такая же, как и для квартала в нашем календарном году. Единственное отличие заключается в том, что мы указываем [FiscalMonth] вместо [Month].
Праздники или особые даты
Можно добавить столбец даты, указывающий, что определенные даты являются праздниками или какой-то другой особой датой. Например, можно подвести итоги продаж за Новый год, добавив поле "Праздники" в сводную таблицу в качестве среза или фильтра. В других случаях может потребоваться исключить эти даты из других столбцов или из меры.
Включить праздники или особые дни довольно просто. В Excel можно создать таблицу с нужными датами. Затем вы можете скопировать или использовать команду "Добавить в модель данных", чтобы добавить ее в модели данных в качестве связанной таблицы. В большинстве случаев нет необходимости создавать связь между таблицей и Calendar таблицей. Любые формулы, ссылающиеся на него, могут использовать функцию 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 | Рождество |
В таблице дат создаем столбец "Праздник" и используем формулу следующего вида.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Рассмотрим эту формулу более внимательно.
Функция LOOKUPVALUE используется для получения значений из столбца "Праздники" в таблице "Праздники". В первом аргументе мы указываем столбец, в котором будет значение результата. Мы указываем столбец "Праздники " в таблице "Праздники ", так как это значение, которое мы хотим вернуть.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Затем мы указываем второй аргумент — столбец поиска с датами, которые нужно найти. Указываем столбец "Дата " в таблице "Праздники " следующим образом:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Наконец, мы указываем столбец в таблице Calendar, содержащий даты, которые мы хотим найти в таблице праздников. Это, конечно же, столбец "Дата" в таблице Calendar.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Столбец "Праздник" возвращает название праздника для каждой строки со значением даты, совпадающим с датой в таблице "Праздники".
Настраиваемый календарь — тринадцать четырехнедельных периодов
Некоторые организации, такие как розничная торговля или общественное питание, часто отчитываются за разные периоды, например за тринадцать четырехнедельных периодов. При тринадцати четырехнедельном календаре каждый период составляет 28 дней; таким образом, каждый период содержит четыре понедельника, четыре вторника, четыре среды и так далее. Каждый период состоит из одинакового количества дней, и, как правило, праздники приходятся на один и тот же период каждый год. Вы можете выбрать начало месячных в любой день недели. Как и для дат в календаре или финансовом году, с помощью DAX можно создавать дополнительные столбцы с настраиваемыми датами.
В приведенных ниже примерах первый полный период начинается в первое воскресенье финансового года. В этом случае финансовый год начинается 01.07.
Неделя
Это значение дает нам номер недели, начиная с первой полной недели финансового года. В этом примере первая полная неделя начинается в воскресенье, поэтому первая полная неделя первого финансового года в таблице Calendar фактически начинается с 04.07.2010 и продолжается до последней полной недели в таблице Calendar. Хотя само по себе это значение не очень полезно для анализа, его необходимо рассчитать для использования в других формулах с 28-дневным периодом.
=INT([date]-40356)/7)
Рассмотрим эту формулу более внимательно.
Сначала мы создадим формулу, которая возвращает значения из столбца "Дата" в виде целого числа:
=ЦЕЛОЕ([дата])
Затем мы хотим найти первое воскресенье в первом финансовом году. Мы видим, что это 04.07.2010.
Теперь вычтите 40356 (то есть целое число для 27.06.2010, последнего воскресенья предыдущего финансового года) из этого значения, чтобы получить количество дней с начала дней в таблице Calendar, следующим образом:
=ЦЕЛОЕ([дата]-40356)
Затем делим полученный результат на 7 (дней в неделе), следующим образом:
=INT(([date]-40356)/7)
Результат выглядит следующим образом:
Period
Период в этом пользовательском календаре состоит из 28 дней и всегда начинается в воскресенье. В этом столбце будет возвращен номер периода, начинающегося с первого воскресенья первого финансового года.
=ЦЕЛОЕ(([Неделя]+3)/4)
Рассмотрим эту формулу более внимательно.
Сначала мы создадим формулу, которая возвращает значение из столбца "Неделя" в виде целого числа:
= INT([Неделя])
Затем добавьте к этому значению 3, как показано ниже.
=ЦЕЛОЕ([неделя]+3)
Затем делим результат на 4, примерно так:
=ЦЕЛОЕ(([Неделя]+3)/4)
Результат выглядит следующим образом:
Период: финансовый год
Это значение возвращает финансовый год для определенного периода.
=ЦЕЛОЕ(([период]+12)/13)+2008
Рассмотрим эту формулу более внимательно.
Сначала мы создаем формулу, которая возвращает значение из Period и складывает 12:
=([Точка]+12)
Делим результат на 13, потому что в финансовом году тринадцать периодов по 28 дней:
=(([Период]+12)/13)
Мы добавляем 2010 год, потому что это первый год в таблице:
=(([Период]+12)/13)+2010
Наконец, мы используем функцию ЦЕЛОЕ, чтобы удалить любую дробную часть результата и вернуть целое число, если его разделить на 13, следующим образом:
= INT(([Period]+12)/13)+2010
Результат выглядит следующим образом:
Период в финансовом году
Это значение возвращает номер периода от 1 до 13, начиная с первого полного периода (начинающегося в воскресенье) каждого финансового года.
=ЕСЛИ(MOD([Period];13); MOD([Period];13);13)
Эта формула немного сложнее, поэтому сначала опишем ее на языке, который нам понятен. Формула гласит: делите значение из [Period] на 13, чтобы получить номер периода (от 1 до 13) в году. Если это число равно 0, возвращается 13.
Сначала мы создаем формулу, которая возвращает остаток значения из Period на 13. Мы можем использовать MOD (математические и тригонометрические функции) следующим образом:
= MOD([Period];13)
Это, по большей части, дает нам нужный результат, за исключением случаев, когда значение Period равно 0, так как эти даты не приходятся на первый финансовый год, как в первых пяти днях нашего примера таблицы дат Calendar. Об этом можно позаботиться с помощью функции ЕСЛИ. Если наш результат равен 0, мы возвращаем 13, например:
= ЕСЛИ(MOD([период];13);MOD([период];13);13)
Результат выглядит следующим образом:
Пример сводной таблицы
На рисунке ниже показана сводная таблица с полем SalesAmount из таблицы фактов продаж в ЗНАЧЕНИЯХ, а также полями PeriodFiscalYear и PeriodInFiscalYear из таблицы измерений даты Calendar в СТРОКАХ. Значение SalesAmount агрегируется для контекста по финансовому году и 28-дневному периоду финансового года.
Отношения
После создания таблицы дат в модели данных, чтобы начать просмотр данных в сводных таблицах и отчетах, а также агрегировать данные по столбцам в таблице измерений даты, необходимо создать связь между таблицей фактов с данными транзакции и таблицей дат.
Так как необходимо создать отношение на основе дат, необходимо создать такую связь между столбцами, значения которых имеют тип данных datetime (Date).
Для каждого значения даты в таблице фактов соответствующий столбец подстановки в таблице дат должен содержать соответствующие значения. Например, строка (запись транзакции) в таблице "Продажи" со значением "15.08.2012 12:00" в столбце DateKey должна иметь соответствующее значение в соответствующем столбце даты в таблице дат (с именем Calendar). Это одна из важнейших причин, по которой нужно, чтобы столбец даты в таблице дат содержал непрерывный диапазон дат, включающий любую возможную дату из таблицы фактов.
Примечание
Хотя столбец даты в каждой таблице должен иметь один и тот же тип данных (Дата), формат каждого столбца не имеет значения.
Примечание
Если Power Pivot не позволяет создать связи между двумя таблицами, в полях дат могут отображаться значения даты и времени с различной степенью точности. В зависимости от форматирования столбца значения могут совпадать, но храниться по-разному. Подробнее о работе со временем.
Примечание
Избегайте использования целочисленных суррогатных ключей в отношениях. При импорте данных из реляционного источника столбцы даты и времени часто представляются суррогатным ключом, который является целочисленным столбцом, используемым для представления уникальной даты. Не следует создавать связи в Power Pivot с помощью целочисленных ключей даты и времени, а вместо этого использовать столбцы, содержащие уникальные значения с типом данных date. Хотя в традиционных хранилищах данных рекомендуется использовать суррогатные ключи, целочисленные ключи не нужны в Power Pivot, что может затруднить группировку значений в сводных таблицах по разным периодам дат.
Если при попытке создать отношение возникает ошибка "Несоответствие типов", скорее всего, это связано с тем, что столбец в таблице фактов не имеет тип данных "Дата". Это может происходить, если Power Pivot не может автоматически преобразовать тип данных, не связанный с датой (обычно текстовый), в тип данных "Дата". Вы по-прежнему можете использовать этот столбец в таблице фактов, но вам придется преобразовать данные с помощью формулы DAX в новый вычисляемый столбец. См. раздел "Преобразование текстовых типов данных дат" в тип данных "Дата" далее в приложении.
Несколько связей
В некоторых случаях может потребоваться создать несколько связей или несколько таблиц дат. Например, если в таблице данных о продажах есть несколько полей дат, таких как DateKey, ShipDate и ReturnDate, все они могут быть связаны с полем даты в таблице дат Calendar, но только одно из них может быть активным отношением. В этом случае, поскольку DateKey представляет собой дату транзакции и, следовательно, наиболее важную дату, она лучше всего подходит для активной связи. У других неактивные отношения.
В следующей сводной таблице общий объем продаж рассчитывается по финансовым годам и кварталам. Мера "Общие продажи" с формулой "Общие продажи:=СУММ([ОбъемПродаж])" помещается в область ЗНАЧЕНИЯ, а поля "Финансовый год" и "Финансовый квартал" из таблицы дат Calendar — в строки.
Эта простая сводная таблица работает правильно, так как мы хотим суммировать наши общие продажи по дате транзакции в DateKey. Наша мера "Общие продажи" использует даты в DateKey и суммируется по финансовым годам и финансовым кварталам, потому что существует связь между DateKey в таблице "Продажи" и столбцом "Дата" в таблице дат Calendar.
Неактивные связи
Но что, если мы хотим подвести итоги наших общих продаж не по дате транзакции, а по дате поставки? Требуется связь между столбцом "ДатаДоставки" в таблице "Продажи" и столбцом "Дата" в таблице Calendar. Если мы не создадим эту связь, наши агрегирования всегда будут основаны на дате транзакции. Однако у нас может быть несколько связей, даже если только одна из них может быть активной, и, поскольку дата транзакции является наиболее важной, она получает активное отношение к таблице Calendar.
В этом случае у ShipDate есть неактивное отношение, поэтому любая формула меры, создаваемая для агрегирования данных на основе дат поставки, должна указывать неактивное отношение с помощью функции USERELATIONSHIP .
Например, из-за отсутствия отношения между столбцом "ДатаПоставки" в таблице "Продажи" и столбцом "Дата" в таблице Calendar можно создать меру, которая суммирует общие продажи по дате поставки. Мы используем такую формулу, чтобы указать используемое отношение:
Общие продажи по дате поставки:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Формула выглядит просто: Вычисление суммы для SalesAmount, но фильтруйте с помощью связи между столбцом ShipDate в таблице Sales и столбцом Date в таблице Calendar.
Теперь, если мы создадим сводную таблицу и поместим меру "Общий объем продаж по дате поставки" в ЗНАЧЕНИЯ, а финансовый год и финансовый квартал в СТРОКИ, мы увидим один и тот же общий итог, но все остальные суммы за финансовый год и финансовый квартал будут отличаться, поскольку они основаны на дате поставки, а не на дате транзакции.
Использование неактивных связей позволяет использовать только одну таблицу дат, но требует, чтобы все показатели (например, общий объем продаж по дате поставки) ссылались в формуле на неактивную связь. Есть и другой вариант — использование нескольких таблиц дат.
Несколько таблиц дат
Еще один способ работы с несколькими столбцами дат в таблице фактов — создание нескольких таблиц дат и создание отдельных активных связей между ними. Давайте еще раз рассмотрим наш пример с таблицей продаж. У нас есть три столбца с датами, по которым мы можем захотеть агрегировать данные:
- Ключ DateKey с датой продажи для каждой проводки.
- Дата отгрузки — с датой и временем отгрузки проданных товаров клиенту.
- Дата возврата — с датой и временем получения одного или нескольких возвращенных элементов.
Помните, что поле DateKey с датой транзакции является наиболее важным. Мы выполним большую часть агрегирования на основе этих дат, поэтому нам наверняка потребуется связь между ним и столбцом даты в таблице Calendar. Если мы не хотим создавать неактивные связи между ShipDate, ReturnDate и полем Date в таблице Calendar, требующие применения специальных формул мер, мы можем создать дополнительные таблицы дат для дат поставки и возврата. Затем мы можем создать активные отношения между ними.
В этом примере мы создали еще одну таблицу дат с именем ShipCalendar. Это, конечно, также означает создание дополнительных столбцов дат, и, поскольку эти столбцы дат находятся в другой таблице дат, мы хотим присвоить им имена таким образом, чтобы они отличались от этих же столбцов в таблице Calendar. Например, мы создали столбцы с именами ShipYear, ShipMonth, ShipQuarter и т. д.
Если мы создадим сводную таблицу и поместим показатель "Общий объем продаж" в значение VALUES, а ShipFiscalYear и ShipFiscalQuarter — в функцию ROWS, мы получим те же результаты, что и при создании неактивного отношения и специального вычисляемого поля Total Sales by Ship Date.
Каждый из этих подходов требует тщательного рассмотрения. При использовании нескольких связей с одной таблицей дат может потребоваться создать специальные меры для транзита неактивных связей с помощью функции USERELATIONSHIP. С другой стороны, создание нескольких таблиц дат может быть запутанным в списке полей, а так как в модели данных больше таблиц, потребуется больше памяти. Поэкспериментируйте с тем, что лучше подходит вам.
Свойство таблицы дат
Свойство "Таблица дат" задает метаданные, необходимые для правильной работы Time-Intelligence функций, таких как TOTALYTD, PREVIOUSMONTH и DATESBETWEEN При выполнении вычисления с использованием одной из этих функций обработчик формул Power Pivot знает, куда нужно поместить нужные даты.
Предупреждение
Если это свойство не задано, меры, использующие функции DAX Time-Intelligence, могут не возвращать правильные результаты.
При задании свойства Таблица дат необходимо указать таблицу дат и столбец дат типа данных Дата (датавремя) в ней.
Задание свойства "Таблица дат"
- В окне PowerPivot выберите таблицу Calendar.
- На вкладке " Конструктор " нажмите кнопку "Пометить как таблицу даты".
- В диалоговом окне "Пометить как таблицу даты" выберите столбец с уникальными значениями и тип данных "Дата".
Работа со временем
Все значения дат с типом данных "Дата" в Microsoft Excel или SQL Server фактически являются числами. В это число включены цифры, которые относятся ко времени. Во многих случаях это время для каждой строки — полночь. Например, если поле DateTimeKey в таблице фактов о продажах содержит значения вида 19.10.2010 12:00:00, это означает, что значения относятся к уровню точности дня. Если значения в поле DateTimeKey содержат время, например 19.10.2010 8:44:00, это означает, что значения имеют точность с точностью до минут. Значения также могут быть с точностью до часов или даже с точностью до секунд. Точность значения времени оказывает существенное влияние на способ создания таблицы дат и связей между ней и таблицей фактов.
Необходимо определить, будете ли вы агрегировать данные с уровнем точности дня или времени. Другими словами, в таблице дат можно использовать столбцы, например "Утро", "Полдень" или "Час", в качестве поля даты в области строки, столбца или фильтра сводной таблицы.
Примечание
Дни — это наименьшая единица времени, с которой могут работать функции DAX Time Intelligence. Если нет необходимости работать со значениями времени, следует уменьшить точность данных, чтобы использовать дни в качестве минимальной единицы.
Если вы хотите агрегировать данные до временного уровня, в таблице дат потребуется столбец даты с включенным временем. Фактически, ему потребуется столбец дат с одной строкой для каждого часа или, может быть, даже каждой минуты каждого дня, для каждого года в диапазоне дат. Это связано с тем, что для создания отношения между столбцом DateTimeKey в таблице фактов и столбцом дат в таблице дат у вас должны быть совпадающие значения. Как вы можете себе представить, если вы включите много лет, это может составить очень большую таблицу дат.
Однако в большинстве случаев требуется агрегировать данные только по дням. Другими словами, вы будете использовать столбцы "Год", "Месяц", "Неделя" или "День недели" в качестве полей в областях строки, столбца или фильтра сводной таблицы. В этом случае столбец дат в таблице дат должен содержать только одну строку для каждого дня года, как описано ранее.
Если столбец даты содержит уровень точности времени, но выполняет агрегирование данных только до уровня дня, для создания связи между таблицей фактов и таблицей дат может потребоваться изменить таблицу фактов, создав новый столбец, в котором значения в столбце даты будут усечены до значения дня. Другими словами, преобразуйте значение, например 19.10.2010 8:44:00 в 19.10.2010 12:00:00. Затем можно создать связь между этим новым столбцом и столбцом дат в таблице дат, поскольку значения совпадают.
Рассмотрим пример. На рисунке показан столбец DateTimeKey в таблице фактов о продажах. Все агрегаты данных в этой таблице должны быть представлены только на уровне дня с помощью столбцов из таблицы Calendar даты, таких как "Год", "Месяц", "Квартал" и т. д. Значением является время, включенное в значение, а не фактическая дата.
Так как нам не нужно анализировать эти данные до уровня времени, нам не нужно, чтобы столбец "Дата" в таблице дат Calendar включал по одной строке для каждого часа и каждой минуты каждого дня в течение года. Итак, столбец "Дата" в нашей таблице дат выглядит следующим образом:
Чтобы создать связь между столбцом DateTimeKey в таблице "Продажи" и столбцом "Дата" в таблице Calendar, можно создать новый вычисляемый столбец в таблице фактов о продажах и использовать функцию ОТБР для усечения значений даты и времени в столбце DateTimeKey в значение даты, соответствующее значениям в столбце "Дата" в таблице Calendar. Наша формула выглядит следующим образом:
=TRUNC([DateTimeKey];0)
В результате мы получим новый столбец (мы назвали его DateKey) с датой из столбца DateTimeKey и временем 12:00:00 для каждой строки:
Теперь мы можем создать связь между этим новым столбцом (DateKey) и столбцом даты в таблице Calendar.
Аналогичным образом мы можем создать вычисляемый столбец в таблице "Продажи", который сократит точность времени в столбце DateTimeKey до уровня точности часов. В этом случае функция ОТБР не будет работать, но мы по-прежнему можем использовать другие функции даты и времени DAX для извлечения и повторной конкатенации нового значения с точностью до часа. Можно использовать такую формулу:
= ДАТА (ГОД([DateTimeKey]), МЕСЯЦ([DateTimeKey]), DAY([DateTimeKey]) ) + ВРЕМЯ (HOUR([DateTimeKey]), 0, 0)
Наша новая колонка выглядит следующим образом:
Если столбец "Дата" в таблице дат содержит значения с часовой точностью, можно создать связь между ними.
Более удобная работа с датами
Многие столбцы дат, которые вы создаете в своей таблице дат, необходимы для других полей, но на самом деле не так уж полезны для анализа. Например, поле DateKey в таблице "Продажи", на которую мы ссылались и показывали в этой статье, важно, так как для каждой транзакции она записывается как проводящаяся в определенную дату и время. Но с точки зрения анализа и отчетности это не так уж и полезно, так как мы не можем использовать его в качестве поля строки, столбца или фильтра в сводной таблице или отчете.
Точно так же в нашем примере столбец "Дата" в таблице Calendar очень полезен и важен, но его нельзя использовать как измерение в сводной таблице.
Чтобы таблицы и столбцы в них были максимально полезными, а также чтобы сводная таблица или список полей Power View были удобными для навигации, важно скрыть ненужные столбцы от средств клиента. Также может потребоваться скрыть некоторые таблицы. Приведенная выше таблица праздников содержит даты праздников, важные для определенных столбцов в таблице Calendar, но их нельзя использовать в таблице "Праздники" в качестве полей сводной таблицы. Чтобы упростить навигацию по спискам полей, можно скрыть всю таблицу праздников.
Еще один важный аспект работы с датами — соглашения об именовании. Присвойте любым другим именам таблицы и столбцы. Но имейте в виду, особенно если вы планируете предоставлять общий доступ к книге другим пользователям, хорошее соглашение об именовании облегчает поиск таблиц и дат не только в списках полей, но и в Power Pivot и в формулах DAX.
Добавив таблицу дат в модель данных, можно приступить к созданию показателей, которые помогут максимально эффективно использовать данные. Некоторые из них могут быть простыми, например подведение итогов продаж за текущий год, а другие — более сложными, когда вам нужно отфильтровать по определенному диапазону уникальных дат. Дополнительные сведения см. в разделе "Меры" в Power Pivot и функциях анализа времени.
Приложение
Преобразование текстового типа данных дат в тип данных даты
Иногда таблица фактов с данными о транзакциях может содержать даты текстового типа данных. Это означает, что дата, которая отображается как 2012-12-04T11:47:09, на самом деле вовсе не является датой, или, по крайней мере, не соответствует типу дат, который может понять Power Pivot. На самом деле это просто текст, который читается как свидание. Чтобы создать связь между столбцом дат в таблице фактов и столбцом дат в таблице дат, оба столбца должны иметь тип данных Date .
Как правило, при попытке изменить тип данных для столбца текстовых данных на тип данных "Дата" Power Pivot может интерпретировать эти даты и автоматически преобразовать их в настоящий тип данных даты. Если Power Pivot не может преобразовать тип данных, возникает ошибка несоответствия типов.
Однако вы все равно можете преобразовать даты в настоящий тип данных "Дата". Вы можете создать новый вычисляемый столбец и использовать формулу DAX для разбора года, месяца, дня, времени и т. д. из текстовых строк, а затем снова соединить их таким образом, чтобы Power Pivot мог считать их истинной датой.
В этом примере мы импортировали таблицу фактов "Продажи" в Power Pivot. Он содержит столбец с именем DateTime. Значения выглядят следующим образом:
Если мы посмотрим на тип данных на вкладке "Главная" в группе "Форматирование" Power Pivot, мы увидим, что это текстовый тип данных.
Не удается создать связь между столбцами "ДатаВремя" и "Дата" в таблице дат, так как типы данных не совпадают. Если мы попытаемся изменить тип данных на Date, мы получим ошибку несоответствия типов:
В этом случае Power Pivot не удалось преобразовать тип данных из текстового в датный. Мы по-прежнему можем использовать этот столбец, но для его добавления в тип данных "Истинная дата" необходимо создать новый столбец, который анализирует текст и повторно создает из него значение. Power Pivot может создать тип данных "Дата".
Помните, из раздела «Работа со временем» ранее в этой статье; Если нет необходимости проводить анализ с точностью времени суток, следует преобразовать даты в таблице фактов в значения точности дня. Исходя из этого, мы хотим, чтобы значения в новом столбце были с дневной точностью (исключая время). Мы можем преобразовать значения в столбце DateTime в тип данных даты и удалить уровень точности времени с помощью следующей формулы:
=ДАТА(ЛЕВСИМВ([ДатаВремени];4), ПСТР([ДатаВремени];6;2), ПСТР([ДатаВремени];9;2))
В результате мы получим новый столбец (в данном случае с именем "Дата"). Power Pivot даже определяет значения как даты и автоматически присваивает тип данных Date.
Если мы хотим сохранить уровень точности времени, мы просто расширяем формулу, чтобы включить часы, минуты и секунды.
=ДАТА(ЛЕВО([ДатаВремени];4), ПСТР([ДатаВремени];6;2), ПСТР([ДатаВремени];9;2)) +
ВРЕМЯ(ПСТР([ДатаВремени];12;2), ПСТР([ДатаВремени];15;2), ПСТР([ДатаВремени];18;2))
Теперь, когда у нас есть столбец "Дата" типа данных "Дата", можно создать связь между ним и столбцом даты в дате.
Дополнительные ресурсы
Краткое руководство. Обучение основам DAX за 30 минут