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

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

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

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

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

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

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

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

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

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

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

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

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

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

• ДАТА

• DATEVALUE

• СЛЕДУЮЩИЙ ДЕНЬ

• EDATE

• EOMONTH

• ЧАС

• МИНУТА

• МЕСЯЦ

• СЕЙЧАС

• СЕКУНДА

• ВРЕМЯ

• TIMEVALUE

• СЕГОДНЯ

• БУДНИЙ ДЕНЬ

• WEEKNUM

• ГОД

• YEARFRAC

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

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

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

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

=FORMAT([date];"mmmm")

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

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

День недели

=FORMAT([date];"ddd")

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Period

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

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

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

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

=INT([Week])

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Даты в PowerPivot

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

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

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

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