Анализ данных включает в себя просмотр данных с течением времени и проведение расчетов за периоды времени. Например, вам, возможно, придется сравнить прибыль текущего года с прибылью предыдущего года. Точно так же вам, возможно, придется прогнозировать рост и прибыль в ближайшие годы. Для этого вам необходимо использовать группирование и агрегирование за определенный период времени.
DAX предоставляет несколько функций Time Intelligence, которые помогают вам выполнять большинство таких вычислений. Однако для этих функций DAX требуется таблица Date для использования с другими таблицами в модели данных.
Вы можете импортировать таблицу Date вместе с другими данными из источника данных или создать таблицу Date самостоятельно в модели данных.
В этой главе вы поймете различные аспекты таблиц дат. Если вы знакомы с таблицами дат в модели данных Power Pivot, вы можете пропустить эту главу и перейти к следующим главам. В противном случае вы можете понять таблицы дат в модели данных Power Pivot.
Что такое таблица дат?
Таблица дат — это таблица в модели данных, имеющая как минимум один столбец непрерывных дат в течение требуемой продолжительности. Он может иметь дополнительные столбцы, представляющие разные периоды времени. Однако необходим столбец непрерывных дат, как того требуют функции DAX Time Intelligence.
Например,
-
В таблице «Дата» могут быть столбцы, такие как «Дата», «Финансовый месяц», «Финансовый квартал» и «Финансовый год».
-
Таблица Date может иметь столбцы, такие как Date, Month, Quarter и Year.
В таблице «Дата» могут быть столбцы, такие как «Дата», «Финансовый месяц», «Финансовый квартал» и «Финансовый год».
Таблица Date может иметь столбцы, такие как Date, Month, Quarter и Year.
Таблица дат с непрерывными датами
Предположим, вам необходимо произвести расчеты в диапазоне календарного года. Затем в таблице «Дата» должен быть хотя бы один столбец с непрерывным набором дат, включая все даты в этом конкретном календарном году.
Например, предположим, что данные, которые вы хотите просмотреть, имеют даты с 1 апреля 2014 года по 30 ноября 2016 года.
-
Если вам необходимо составить отчет за календарный год, вам нужна таблица «Дата» с колонкой «Дата», которая содержит все даты с 1 января 2014 года по 31 декабря 2016 года в последовательности.
-
Если вам нужно отчитаться о финансовом году, а конец финансового года — 30 июня, вам нужна таблица дат со столбцом — Дата, которая содержит все даты с 1 июля 2013 года по 30 июня 2017 года в последовательность.
-
Если вам нужно отчитываться как по календарному, так и по финансовому году, то вы можете иметь одну таблицу дат, охватывающую требуемый диапазон дат.
Если вам необходимо составить отчет за календарный год, вам нужна таблица «Дата» с колонкой «Дата», которая содержит все даты с 1 января 2014 года по 31 декабря 2016 года в последовательности.
Если вам нужно отчитаться о финансовом году, а конец финансового года — 30 июня, вам нужна таблица дат со столбцом — Дата, которая содержит все даты с 1 июля 2013 года по 30 июня 2017 года в последовательность.
Если вам нужно отчитываться как по календарному, так и по финансовому году, то вы можете иметь одну таблицу дат, охватывающую требуемый диапазон дат.
Ваша таблица дат должна содержать все дни для диапазона каждого года в данной продолжительности. Таким образом, вы получите смежные даты в течение этого периода времени.
Если вы регулярно обновляете свои данные новыми данными, конечная дата будет продлена на год или два, чтобы вам не приходилось часто обновлять таблицу дат.
Таблица дат выглядит следующим образом.
Добавление таблицы дат в модель данных
Вы можете добавить таблицу Date в модель данных любым из следующих способов:
-
Импорт из реляционной базы данных или любого другого источника данных.
-
Создание таблицы дат в Excel, а затем копирование или связывание с новой таблицей в Power Pivot.
-
Импорт из Microsoft Azure Marketplace.
Импорт из реляционной базы данных или любого другого источника данных.
Создание таблицы дат в Excel, а затем копирование или связывание с новой таблицей в Power Pivot.
Импорт из Microsoft Azure Marketplace.
Создание таблицы дат в Excel и копирование в модель данных
Создание таблицы дат в Excel и копирование в модель данных — это самый простой и гибкий способ создания таблицы данных в модели данных.
-
Откройте новый лист в Excel.
-
Тип — Дата в первой строке столбца.
-
Введите первую дату в диапазоне дат, который вы хотите создать, во второй строке в том же столбце.
-
Выберите ячейку, щелкните маркер заполнения и перетащите его вниз, чтобы создать столбец непрерывных дат в необходимом диапазоне дат.
Откройте новый лист в Excel.
Тип — Дата в первой строке столбца.
Введите первую дату в диапазоне дат, который вы хотите создать, во второй строке в том же столбце.
Выберите ячейку, щелкните маркер заполнения и перетащите его вниз, чтобы создать столбец непрерывных дат в необходимом диапазоне дат.
Например, введите 01.01.2014, щелкните маркер заполнения и перетащите вниз, чтобы заполнить непрерывные даты до 31.12.2016.
- Нажмите столбец Дата.
- Нажмите вкладку INSERT на ленте.
- Нажмите Таблица.
- Проверьте диапазон таблицы.
- Нажмите ОК.
Таблица одного столбца дат готова в Excel.
- Выберите стол.
- Нажмите Копировать на ленте.
- Нажмите на окно Power Pivot.
- Нажмите Вставить на ленте.
Это добавит содержимое буфера обмена в новую таблицу в модели данных. Следовательно, вы можете использовать тот же метод для создания таблицы Date в существующей модели данных.
Откроется диалоговое окно «Вставить предварительный просмотр», как показано на следующем снимке экрана.
- Введите Дата в поле Имя таблицы.
- Предварительный просмотр данных.
- Установите флажок — Использовать первую строку в качестве заголовков столбцов.
- Нажмите ОК.
Это копирует содержимое буфера обмена в новую таблицу в модели данных.
Теперь у вас есть таблица Date в модели данных с одним столбцом непрерывных дат. Заголовок столбца — это дата, указанная в таблице Excel.
Добавление новых столбцов даты в таблицу дат
Затем вы можете добавить вычисляемые столбцы в таблицу Date согласно требованию для ваших расчетов.
Например, вы можете добавить столбцы — День, Месяц, Год и Квартал следующим образом —
- День
= ДЕНЬ ( ‘Date’ [Дата])
- Месяц
= МЕСЯЦ ( ‘Date’ [Дата])
- Год
= YEAR ( ‘Date’ [Дата])
- четверть
= CONCATENATE («QTR», INT ((‘Date’ [Month] +2) / 3))
= ДЕНЬ ( ‘Date’ [Дата])
= МЕСЯЦ ( ‘Date’ [Дата])
= YEAR ( ‘Date’ [Дата])
= CONCATENATE («QTR», INT ((‘Date’ [Month] +2) / 3))
Результирующая таблица Date в модели данных выглядит следующим образом.
Таким образом, вы можете добавить любое количество вычисляемых столбцов в таблицу Date. Что важно и необходимо, так это то, что в таблице «Дата» должен быть столбец непрерывных дат, охватывающий промежуток времени, в течение которого вы выполняете вычисления.
Создание таблицы дат для календарного года
Календарный год обычно включает в себя даты с 1 января по 31 декабря года, а также праздничные дни, отмеченные для этого конкретного года. При выполнении расчетов может потребоваться учитывать только рабочие дни, исключая выходные и праздничные дни.
Предположим, вы хотите создать таблицу Date для календарного года 2017.
-
Создайте таблицу Excel со столбцом «Дата», состоящим из непрерывных дат с 1 января 2017 года по 31 декабря 2017 года. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)
-
Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)
-
Назовите таблицу как Календарь.
-
Добавьте следующие рассчитанные столбцы —
-
День = ДЕНЬ («Календарь» [Дата])
-
Месяц = МЕСЯЦ («Календарь» [Дата])
-
Год = ГОД («Календарь» [Дата])
-
День недели = ФОРМАТ («Календарь» [Дата], «DDD»)
-
Название месяца = ФОРМАТ («Календарь» [Дата], «МММ»)
-
Создайте таблицу Excel со столбцом «Дата», состоящим из непрерывных дат с 1 января 2017 года по 31 декабря 2017 года. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)
Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)
Назовите таблицу как Календарь.
Добавьте следующие рассчитанные столбцы —
День = ДЕНЬ («Календарь» [Дата])
Месяц = МЕСЯЦ («Календарь» [Дата])
Год = ГОД («Календарь» [Дата])
День недели = ФОРМАТ («Календарь» [Дата], «DDD»)
Название месяца = ФОРМАТ («Календарь» [Дата], «МММ»)
Добавление праздников в таблицу календаря
Добавьте праздники в таблицу календаря следующим образом —
-
Получить список объявленных праздников за год.
-
Например, для США вы можете получить список праздников для любого требуемого года по следующей ссылке http://www.calendar-365.com/ .
-
Скопируйте и вставьте их в лист Excel.
-
Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных.
-
Назовите стол как Праздники.
Получить список объявленных праздников за год.
Например, для США вы можете получить список праздников для любого требуемого года по следующей ссылке http://www.calendar-365.com/ .
Скопируйте и вставьте их в лист Excel.
Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных.
Назовите стол как Праздники.
-
Затем вы можете добавить вычисляемый столбец выходных дней в таблицу Calendar, используя функцию DAX LOOKUPVALUE.
Затем вы можете добавить вычисляемый столбец выходных дней в таблицу Calendar, используя функцию DAX LOOKUPVALUE.
= LOOKUPVALUE (праздники [Праздник], Праздники [Дата], ‘Календарь’ [Дата])
Функция DAX LOOKUPVALUE ищет третий параметр, т.е. Calendar [Date], во втором параметре, т.е. Holidays [Date], и возвращает первый параметр, т.е. Holidays [Holiday], если есть совпадение. Результат будет выглядеть так, как показано на следующем скриншоте.
Добавление столбцов в финансовый год
Финансовый год обычно включает даты с 1- го числа месяца после окончания финансового года до следующего финансового года. Например, если конец финансового года — 31 марта, финансовый год колеблется с 1 апреля по 31 марта.
Вы можете включить финансовые периоды времени в таблицу календаря, используя формулы DAX —
-
Добавить меру для FYE
FYE: = 3
-
Добавьте следующие рассчитанные столбцы —
-
Отчетный год
= ЕСЛИ ( ‘Календарь’ [Месяц] <= ‘Календарь’ [FYE], ‘календарь’ [год], ‘календарь’ [год] +1)
-
Финансовый месяц
= IF («Календарь» [Месяц] <= «Календарь» [FYE], 12-«Календарь» [FYE] + «Календарь» [Месяц], «Календарь» [Месяц] — «Календарь» [FYE])
-
Фискальный квартал
= INT ((‘Календарь’ [Финансовый месяц] +2) / 3)
-
Добавить меру для FYE
FYE: = 3
Добавьте следующие рассчитанные столбцы —
Отчетный год
= ЕСЛИ ( ‘Календарь’ [Месяц] <= ‘Календарь’ [FYE], ‘календарь’ [год], ‘календарь’ [год] +1)
Финансовый месяц
= IF («Календарь» [Месяц] <= «Календарь» [FYE], 12-«Календарь» [FYE] + «Календарь» [Месяц], «Календарь» [Месяц] — «Календарь» [FYE])
Фискальный квартал
= INT ((‘Календарь’ [Финансовый месяц] +2) / 3)
Установка свойства таблицы даты
При использовании функций DAX Time Intelligence, таких как TOTALYTD, PREVIOUSMONTH и DATESBETWEEN, для корректной работы требуются метаданные. Свойство таблицы дат устанавливает такие метаданные.
Чтобы установить свойство таблицы дат —
- Выберите таблицу календаря в окне Power Pivot.
- Перейдите на вкладку «Дизайн» на ленте.
- Нажмите Пометить как таблицу дат в группе Календари.
- Нажмите Mark as Date Table в раскрывающемся списке.
Откроется диалоговое окно «Пометить как таблицу дат». Выберите столбец «Дата» в таблице «Календарь». Это должен быть столбец типа данных Date и должен иметь уникальные значения. Нажмите ОК.