Создание таблицы дат
При создании отчетов в Power BI организациям чаще всего требуется выполнение расчетов на основе даты и времени. Организации хотят иметь представление, как продвигался их бизнес за месяцы, кварталы, финансовые годы и т. д. Поэтому крайне важно, чтобы значения с привязкой ко времени были правильно отформатированы. Power BI автоматически обнаруживает столбцы и таблицы дат; однако в некоторых ситуациях вам нужно будет предпринять дополнительные действия, чтобы получить даты в формате, который требуется организации.
Например, предположим, что вы разрабатываете отчеты для отдела продаж в вашей организации. База данных содержит таблицы для продаж, заказов, продуктов и др. Вы заметили, что многие из этих таблиц, включая Sales и Orders, содержат свои собственные столбцы даты, как, например, столбцы ShipDate и OrderDate в таблицах Sales и Orders. Вам поручено разработать таблицу общего объема продаж и заказов по годам и месяцам. Как можно создать визуальный объект с несколькими таблицами, каждая из которых ссылается на свои собственные столбцы даты?
Чтобы решить эту проблему, вы можете создать общую таблицу дат, которая будет использоваться несколькими таблицами. В следующем разделе объясняется, как выполнить эту задачу в Power BI.
Создание общей таблицы дат
Существует несколько способов построения общей таблицы дат:
исходные данные;
DAX;
Power Query.
Исходные данные
Иногда исходные базы данных и хранилища данных уже имеют свои собственные таблицы дат. Если администратор, разрабатывавший базу данных, проделал тщательную работу, эти таблицы можно использовать для выполнения следующих задач:
определения корпоративных отпусков;
разделения календарного и финансового годов;
определения выходных и рабочих дней.
Таблицы исходных данных являются продуманными и готовы к немедленному использованию. Если у вас есть таблица как таковая, принесите ее в семантическую модель и не используйте другие методы, описанные в этом разделе. Мы рекомендуем использовать исходную таблицу дат, так как, скорее всего, ее используют и другие инструменты, которые вы можете использовать в дополнение к Power BI.
Если у вас нет исходной таблицы данных, воспользуйтесь другими способами создания общей таблицы дат.
DAX
Вы можете построить общую таблицу дат с помощью функций Data Analysis Expression (DAX) CALENDARAUTO() или CALENDAR(). Функция CALENDAR() возвращает непрерывный диапазон дат на основе дат начала и окончания, которые вводятся в функцию как аргументы. Кроме того, функция CALENDARAUTO() возвращает непрерывный полный диапазон дат, которые автоматически определяются на основе семантической модели. Начальная дата выбирается как самая ранняя в вашей семантической модели, а конечная дата — это последняя дата, существующая в вашей семантической модели, а также данные, заполненные финансовым месяцем, который можно включить в качестве аргумента в функцию CALENDARAUTO(). Для целей этого примера используется функция CALENDAR(), потому что вам нужно видеть только данные с 31 мая 2011 года (первый день, когда в таблице Sales эти данные начали отслеживаться) и в течение следующих 10 лет.
В Power BI Desktop выберите Создать таблицу и введите следующую формулу DAX:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Теперь у вас есть столбец дат, который можно использовать. Однако этого столбца мало. Вам также нужны столбцы только для года, номера месяца, недели года и дня недели. Чтобы выполнить эту задачу, нажмите Создать столбец на ленте и введите следующее уравнение DAX, которое будет извлекать год из таблицы Date.
Year = YEAR(Dates[Date])
Выполняя такой же процесс, можно получить столбцы с номерами месяцев, номерами недель и днями недели:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Когда вы закончите, ваша таблица будет содержать столбцы, показанные на следующем рисунке.
Теперь вы создали общую таблицу дат с помощью DAX. Этот процесс только добавляет новую таблицу в семантику; Вам по-прежнему потребуется установить связи между таблицей дат и таблицами Sales и Order, а затем пометить таблицу как официальную таблицу дат семантической модели. Однако сначала давайте рассмотрим другой способ построения общей таблицы дат — с помощью Power Query.
Power Query
Вы можете определить общую таблицу дат с помощью языка разработки M, который используется для построения запросов в Power Query.
Выберите Преобразовать данные в Power BI Desktop, и вы будете перенаправлены в Power Query. Щелкните правой кнопкой мыши в пустом месте левой панели Запросы. В появившемся раскрывающемся меню, которое показано ниже, выберите Создать запрос > Пустой запрос.
В открывшемся представлении Создание запроса введите следующую M-формулу для построения таблицы календаря:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
В отношении данных о продажах вам нужно, чтобы дата начала отражала самую раннюю дату в ваших данных: 31 мая 2011 года. Кроме того, вы хотите видеть даты на следующие 10 лет, включая даты, которые еще не наступили. Такой подход гарантирует, что при поступлении новых данных о продажах вам не придется заново создавать эту таблицу. Вы также можете изменить продолжительность. В данном случае вам нужна точка данных для каждого дня, но вы также можете увеличивать ее на часы, минуты и секунды. На следующем рисунке показан результат.
Успешно справившись с этим процессом, вы видите, что у вас появился список дат вместо таблицы дат. Чтобы исправить эту ошибку, перейдите на вкладку Преобразование на ленте и выберите Преобразовать > В таблицу. Как понятно из названия, эта функция преобразует ваш список в таблицу. Вы также можете переименовать столбец в DateCol.
Далее вам нужно добавить столбцы в новую таблицу, чтобы можно было видеть даты по годам, месяцам, неделям и дням для построения иерархии в визуальном объекте. Первая задача — изменить тип столбца. Для этого щелкните значок рядом с именем столбца и в появившемся раскрывающемся меню выберите тип Дата.
Завершив выбор типа Дата, добавьте столбцы для года, месяца, недели и дня. Перейдите в раздел Добавить столбец, нажмите стрелку раскрывающегося меню под заголовком Дата, а затем выберите Год, как показано на следующем рисунке.
Обратите внимание, что Power BI добавил столбец всех лет, извлеченных из столбца DateCol.
Выполните этот же процесс для месяцев, недель и дней. Когда вы закончите, таблица будет содержать столбцы, показанные на следующем рисунке.
Итак, вы успешно использовали Power Query для создания общей таблицы дат.
На предыдущих шагах показано, как получить таблицу в семантическую модель. Теперь эту таблицу нужно пометить как принятую таблицу дат, чтобы Power BI мог распознавать ее при добавлении всех будущих значений, и проверить правильность форматирования.
Пометка таблицы как принятой таблицы дат
Чтобы пометить таблицу как официальную таблицу дат, сначала найдите эту новую таблицу в панели Поля. Щелкните правой кнопкой мыши имя таблицы и выберите Пометить как таблицу дат, как показано на следующем рисунке.
Помечая вашу таблицу как таблицу дат, Power BI выполняет проверки, чтобы убедиться, что в данных отсутствуют значения NULL, эти данные уникальны и содержат непрерывные значения даты за нужный период. Вы также можете выбрать конкретные столбцы в своей таблице, чтобы пометить их как столбцы дат, что может быть полезно, если в вашей таблице много столбцов. Щелкните таблицу правой кнопкой мыши, выберите Пометить как таблицу дат, а затем выберите Параметры таблицы дат. Откроется следующее окно, в котором можно выбрать столбец, который должен быть помечен как Дата.
При выборе пункта Пометить как таблицу дат автоматически сгенерированные иерархии будут удалены из поля Дата таблицы, которую вы пометили как таблицу дат. Для других полей даты автоматическая иерархия будет сохраняться до тех пор, пока вы не установите связь между этим полем и таблицей дат или пока не отключите функцию Автоматическая дата/время. Вы можете вручную добавить иерархию в общую таблицу дат, щелкнув правой кнопкой мыши столбцы года, месяца, недели или дня в панели Поля и выбрав Создать иерархию. Этот процесс более подробно рассматривается ниже в этом модуле.
Построение визуального объекта
Для построения визуального объекта между таблицами Sales и Orders вам необходимо установить отношение между этой новой общей таблицей дат и таблицами Sales и Orders. В результате вы сможете строить визуальные объекты, используя новую таблицу дат. Для выполнения этой задачи выберите вкладку Модель>Управление связями, где можно создать отношения между общей таблицей дат и таблицами Orders и Sales с помощью столбца OrderDate. На следующем снимке экрана показан пример одного такого отношения.
После построения отношений вы можете создать визуальный объект Общий объем продаж и количество заказов по времени, используя общую таблицу дат, которая была разработана с помощью DAX или Power Query.
Чтобы определить общий объем продаж, необходимо добавить все продажи, так как в столбце Amount таблицы Sales учитывается только доход от каждой продажи, а не общий доход от продаж. Чтобы выполнить эту задачу, можно использовать вычисление следующей меры, которое будет объяснено далее. Вычисление, которое будет использоваться при построении этой меры, выглядит следующим образом:
#Total Sales = SUM(Sales[‘Amount’])
После завершения вы можете создать таблицу, вернувшись на вкладку Визуализации и выбрав визуальный объект Таблица. Вам нужно видеть общее количество заказов и продаж по годам и месяцам, поэтому достаточно включить столбцы Year и Month из таблицы дат, столбец OrderQty и меру #TotalSales. После изучения иерархий вы также сможете построить иерархию, которая позволит выполнять детализацию, переходя от года к месяцу. В этом примере вы можете просматривать эти данные параллельно. Итак, вы успешно создали визуальный объект с помощью общей таблицы дат.
