Перейти к основному контенту
Office

Создание таблиц дат в PowerPivot для Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

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

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

Содержание

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

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

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

Создание таблицы дат в Excel

Инструкции: создание таблицы дат в Excel и ее копирование в модель данных

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

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

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

Год

Месяц

Квартал

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

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

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

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

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

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

Настраиваемый календарь — 13 4 – периоды недели

Неделя

Период

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

Период в Фискалеар

Отношения

Множественные связи

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

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

Свойство "таблица дат"

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

Создание более удобных дат

Приложение

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

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

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

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

Отчет Power View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создание таблицы дат в Excel

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

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

Инструкции: создание таблицы дат в Excel и ее копирование в модель данных

  1. В Excel на пустом листе в ячейке a1введите имя заголовка столбца, чтобы определить диапазон дат. Как правило, это будетвыглядеть примерно так, как дата, Дата и ключдаты.

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

  3. Щелкните маркер заполнения и перетащите его на номер строки, включающий дату окончания. Например, 12/31/2016.

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

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

  5. В группе стили нажмите кнопку Форматировать как таблицуи выберите нужный стиль.

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

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

  7. Копирование всех строк, включая верхний колонтитул.

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

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

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

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

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

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

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

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

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

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

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

Совет: Если вы еще не работали с DAX, лучше начать обучение с помощью краткоГо руководства: обучение основам DAX через 30 минут на Office.com.

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

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

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

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

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

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

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

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

Существует множество других функций DAX, которые можно использовать в формулах. Например, многие формулы, описанные здесь, используют математические и тригонометрические функции , такие как Mod и ОТБР, логические функции , такие как Если, и текстовые функции вроде Format для получения дополнительных сведений о другие функции DAX можно найти в разделе Дополнительные ресурсы ниже в этой статье.

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

В следующих примерах описаны формулы, используемые для создания дополнительных столбцов в таблице дат с именем Calendar. Один столбец с именем Date уже существует и включает непрерывный диапазон дат от 1/1/2010 до 12/31/2016.

Год

= YEAR ([Дата])

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

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

Месяц

= MONTH ([Дата])

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

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

Квартал

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

В этой формуле функция int используется для возврата значения даты в виде целого числа. Аргумент, который мы указываем для функции ЦЕЛОе, — это значение из столбца month, сложение 2, а затем деление на 3 для получения четвертого квартала, 1 – 4.

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

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

= FORMAT ([Дата]; "MMMM")

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

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

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

День недели

= FORMAT ([Дата]; "ddd")

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

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

Если у вас есть поля для дат, например год, квартал, месяц и т. д., вы можете использовать их в сводной таблице или отчете. Например, на приведенном ниже рисунке показано поле SalesAmount из таблицы фактов продаж в ЗНАЧЕНИЯх, год и квартал из таблицы измерение календаря в СТРОКах. SalesAmount обрабатывается для контекста года и квартала.

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

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

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

= Если ([Month] _Лт_ = 6; [год]; [год] + 1)

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

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

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

Еще один способ задать значение месяца окончания финансового года — создать меру, которая просто указывает месяц. Например, Фе: = 6. Затем вы можете добавить ссылку на название меры вместо номера месяца. Например, = если ([Month] _Лт_ = [Фе]; [год]; [год] + 1). Это обеспечивает более гибкое обращение к месяцу окончания финансового года в нескольких разных формулах.

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

= Если ([Month] _Лт_ = 6; 6 + [Month]; [Month]-6)

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

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

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

= INT (([Фискалмонс] + 2)/3)

Формула, используемая для Фискалкуартер, имеет такой же размер, что и для квартала в нашем календарном году. Единственная разница заключается в том, что мы указываем [Фискалмонс] вместо [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

Рождества

В таблице Date мы создаем столбец " праздник " и используем формулу, например:

= LOOKUPVALUE (праздники [праздник]; праздники [Дата], календарь [Дата])

Рассмотрим эту формулу более аккуратно.

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

= LOOKUPVALUE (праздники [праздник]; праздники [Дата], календарь [Дата])

Затем мы указываем второй аргумент — столбец поиска с датами, которые нужно найти. В таблице праздников мы указываем столбец Date (Дата ), как показано ниже.

= LOOKUPVALUE (праздники [праздник];праздники [Дата], календарь [Дата])

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

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

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

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

Настраиваемый календарь — 13 4 – периоды недели

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

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

Неделя

Это значение дает нам номер недели, начиная с первой полной недели в финансовом году. В этом примере первая полная неделя начинается на воскресенье, поэтому первая полная неделя в первом финансовом году в таблице "Календарь" начинается с 7/4/2010 и продолжается за последнюю полную неделю в таблице "Календарь". Несмотря на то, что это значение не является полезным при анализе, необходимо вычислить использование в других формулах периода по 28 дням.

= INT ([Дата]-40356)/7)

Рассмотрим эту формулу более аккуратно.

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

= INT ([Дата])

Затем мы хотели бы найти первое воскресенье в первом финансовом году. Мы видим, что это 7/4/2010.

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

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

= INT ([Дата]-40356)

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

= INT (([Дата]-40356)/7)

Результат выглядит так:

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

Период

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

= INT (([неделя] + 3)/4)

Рассмотрим эту формулу более аккуратно.

Сначала создадим формулу, которая возвращает значение из столбца "Неделя" в виде целого числа, как показано ниже.

=Int ([неделя])

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

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

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

= INT (([неделя] + 3)/4)

Результат выглядит так:

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

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

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

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

Рассмотрим эту формулу более аккуратно.

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

= ([Period] + 12)

Результат делится на 13, так как в финансовом году есть 13 28 дней.

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

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

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

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

=Int(([period] + 12)/13)+2010

Результат выглядит так:

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

Период в Фискалеар

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

= Если (MOD ([period]; 13); MOD ([period]; 13); 13)

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

Сначала создадим формулу, которая возвращает остаток значения от period до 13. Мы можем использовать функцию остат (математические и тригонометрические функции), как показано ниже.

=Mod ([period]; 13)

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

=Если(MOD ([period]; 13); Mod ([period]; 13); 13)

Результат выглядит так:

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

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

На рисунке ниже показана сводная таблица с полем SalesAmount из таблицы фактов продажи в ЗНАЧЕНИЯх, а также Периодфискалеар и Периодинфискалеар полей из таблицы измерение Дата календаря в СТРОКах. SalesAmount является агрегатной для контекста за финансовый год и 28-дневный период в финансовом году.

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

Отношения

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

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

Для каждого значения даты в таблице фактов связанный столбец подстановки в таблице дат должен содержать совпадающие значения. Например, для строки (записи транзакции) в таблице фактов Sales со значением 8/15/2012 12:00 AM в столбце Ключдаты должно быть соответствующее значение в соответствующем столбце "Дата" в таблице Date (именованный календарь). Это является одной из наиболее важных причин, по которым вы хотите, чтобы столбец даты в таблице дат содержал непрерывный диапазон дат, включающий любую возможную дату в таблице фактов.

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

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

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

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

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

Множественные связи

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

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

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

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

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

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

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

Например, поскольку существует неактивная связь между столбцом ShipDate в таблице Sales и столбцом Дата в таблице Календарь, мы можем создать меру, которая суммирует общие продажи по дате отгрузки. Чтобы указать используемую связь, мы используем такую формулу, как показано ниже.

Объем продаж по дате отГрузки: = ВЫЧИСЛЕНие (SUM (Sales [SalesAmount]), USERELATIONSHIP (продажи [ShipDate]); календарь [Дата]))

Эта формула просто возвращает сумму для SalesAmount, но фильтр с использованием связи между столбцом ShipDate в таблице "продажи" и столбцом "Дата" в таблице "Календарь".

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

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

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

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

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

  • Ключдаты с датой продажи для каждой транзакции.

  • ShipDate — Дата и время, когда проданные товары были отгружены клиенту.

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

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

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

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

Если мы создаем нашу сводную таблицу и поместием в нее сумму продаж и Шипфискалеар и Шипфискалкуартер в СТРОКах, мы выбираю те же результаты, что и при создании неактивной связи, и вычисляемое поле «Сумма продаж по дате отГрузки».

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

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

Свойство "таблица дат"

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

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

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

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

Инструкции: Установка свойства "таблица дат"

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

  2. На вкладке конструктор нажмите кнопку помечать как таблицу дат.

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

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

Все значения дат с типом данных "Дата" в Excel или SQL Server фактически являются числами. В этот номер входят цифры, которые ссылаются на время. Во многих случаях это время для каждой строки — полночь. Например, если поле "Ключдатыивремени" в таблице фактов Sales имеет значения, такие как 10/19/2010 12:00:00 AM, это означает, что значения представляют собой дневной уровень точности. Если в значениях полей "Ключдатыивремени" задано время, например 10/19/2010 8:44:00 AM, это означает, что значения имеют уровень точности "точность". Значения могут быть также возможной с точностью, равной часовому уровню, или даже с уровнем точности. Уровень точности в значении времени будет существенно влиять на создание таблицы дат и связей между ней и таблицей фактов.

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

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

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

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

Если в столбце "Дата" указан часовой уровень точности, но для создания связи между таблицей фактов и таблицей дат требуется статистическая обработка, возможно, потребуется изменить таблицу фактов, создав новый столбец, который усекает значения в дате c. олумн к значению дня. Другими словами, преобразуйте значение, например 10/19/2010 8:44:0010/19/2010 12:00:00 AM. Затем вы можете создать связь между этим столбцом и столбцом Date в таблице Date, так как значения совпадают.

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

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

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

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

Чтобы создать связь между столбцом "Ключдатыивремени" в таблице "продажи" и столбцом "Дата" в таблице "Календарь", мы можем создать новый вычисляемый столбец в таблице фактов Sales и использовать функцию ОТБР для усечения значения даты и времени в "ключдатыивремени"е. столбец в значение даты, которое соответствует значениям в столбце "Дата" в таблице "Календарь". Наша формула выглядит так:

= ОТБР (["Ключдатыивремени"]; 0)

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

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

Теперь мы можем создать связь между этим новым столбцом (Ключдаты) и столбцом "Дата" в таблице "Календарь".

Аналогичным образом мы можем создать вычисляемый столбец в таблице Sales, который сокращает время в столбце "Ключдатыивремени" до часового уровня точности. В этом случае функция ОТБР не будет работать, но мы по-прежнему можем использовать другие функции даты и времени DAX для извлечения и повторного сцепления нового значения с часовым уровнем точности. Вот как можно использовать такую формулу:

= Дата (год (["Ключдатыивремени"]); месяц (["Ключдатыивремени"]); день (["Ключдатыивремени"])) + время (час (["Ключдатыивремени"]); 0; 0)

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

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

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

Создание более удобных дат

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

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

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

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

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

Приложение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= Дата (LEFT ([DateTime]; 4); MID ([DateTime]; 6; 2); MID ([DateTime]; 9; 2)) +

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

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

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

Даты в PowerPivot

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

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

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

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

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

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

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

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×