Таблиці дат у надбудові Power Pivot необхідні, щоб переглядати й обчислювати дані з плином часу. У цій статті наведено докладні відомості про таблиці дат і про те, як їх можна створити в Power Pivot. Зокрема, у цій статті описано:
- Чому таблиця дат важлива для перегляду й обчислень даних за датами й часом?
- Використання надбудови Power Pivot для додавання таблиці дат до моделі даних.
- Дізнайтеся, як створити нові стовпці дати в таблиці дат, такі як "Рік", "Місяць" і "Період".
- Дізнайтеся, як створити зв'язки між таблицями дат і таблицями фактів.
- Як працювати з часом.
Цю статтю призначено для користувачів, які не мають досвіду роботи з надбудовою Power Pivot. Проте важливо розуміти, як імпортувати дані, створювати зв'язки, а також обчислювані стовпці й міри.
У цій статті не описано, як використовувати функції DAX Time-Intelligence у формулах мір. Докладні відомості про створення мір за допомогою функцій часового аналізу DAX див. в статті "Часовий аналіз" у надбудові 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 або створте з нею зв'язок.
- Import from Microsoft AzureAzure 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).
У групі "Стилі" натисніть кнопку "Форматувати як таблицю" та виберіть потрібний стиль.
У діалоговому вікні "Формат таблиці" натисніть кнопку "OK".
Скопіюйте всі рядки, зокрема заголовок.
У надбудові Power Pivot на вкладці "Основне " натисніть кнопку "Вставити".
У вікні попереднього перегляду>вставлення введіть ім'я, наприклад Date або CalendarCalendar. Залиште прапорець "Використовувати перший рядок як заголовки стовпців" і натисніть кнопку "OK".
Нова таблиця дат (у цьому прикладі – CalendarCalendar) у надбудові Power Pivot має такий вигляд:
Примітка.
Зв'язану таблицю також можна створити за допомогою команди "Додати до моделі даних". Однак через це книга має невиправдано великі розміри, оскільки в ній є дві версії таблиці дат. один у програмі Excel і один у надбудові Power Pivot.
Примітка.
Дата імені – це ключове слово в надбудові Power Pivot. Якщо назвати таблицю, створену в надбудові Power Pivot, потрібно буде брати ім'я таблиці в одинарні лапки в усіх формулах DAX, які посилаються на неї в аргументі. У всіх прикладах зображень і формул у цій статті йдеться про таблицю дат, створену в надбудові Power Pivot, з ім'ям CalendarCalendar.
Тепер у вашій моделі даних є таблиця дат. Використовуючи мову DAX, можна додавати нові стовпці дат, наприклад "Рік", "Місяць" тощо.
Додавання нових стовпців дат до таблиці дат
Щоб визначити всі дати в певному проміжку, таблицю дат з одним стовпцем дат з одним рядком на кожен день кожного року. Він також необхідний для створення зв'язку між таблицями фактів і дат. Але такий один стовпець дат із одним рядком на кожен день не підходить для аналізу даних за датами у зведеній таблиці або у звіті Power View. Ви хочете, щоб таблиця дат включала стовпці, які допомагають агрегувати дані для діапазону або групи дат. Наприклад, можна підсумувати обсяги продажів за місяцями чи кварталами або створити міру, яка обчислює зростання за минулий рік. У кожному з цих випадків у таблиці дат необхідні стовпці року, місяця або кварталу, які дають змогу агрегувати дані за відповідний період.
Якщо ви імпортували таблицю дат із реляційного джерела даних, вона вже може містити потрібні стовпці дат різних типів. У деяких випадках може знадобитися змінити деякі з цих стовпців або створити додаткові стовпці дат. Це особливо вірогідно, якщо ви створюєте власну таблицю дат в Excel і копіюєте її в модель даних. На щастя, створити нові стовпці дат у Power Pivot досить легко завдяки функціям дати й часу в DAX.
Порада.
Якщо ви ще не працювали з DAX, то почати навчання можна з короткого посібника: вивчіть основи мови DAX за 30 хвилин на Office.com.
Функції дати й часу DAX
Якщо ви коли-небудь працювали з функціями дати й часу у формулах Excel, то напевно вже знайомі з функціями дати й часу. Хоча ці функції схожі на аналоги в програмі Excel, між ними є деякі важливі відмінності:
- У функціях дати й часу DAX використовується тип даних дати й часу.
- Вони можуть використовувати значення зі стовпця як аргумент.
- Їх можна використовувати, щоб повертати та/або керувати значеннями дат.
Ці функції часто використовуються під час створення настроюваних стовпців дат у таблиці дат, тому їх важливо розуміти. Ми використаємо ці функції, щоб створити стовпці "Рік", "Квартал", "ФінансовийМісяць" тощо.
Примітка.
Функції дати й часу в DAX відрізняються від функцій часового аналізу. Дізнайтеся більше про часовий аналіз у надбудові Power Pivot для Excel.
Мова DAX містить такі функції дати й часу:
- DATE (ДАТА)
- DATEVALUE
- НАСТУПНИЙ ДЕНЬ
- EDATE
- EOMONTH
- ГОДИНА
- ХВИЛИНА
- МІСЯЦЬ
- NOW
- СЕКУНДА
- TIME
- TIMEVALUE
- СЬОГОДНІ
- WEEKDAY
- WEEKNUM
- РІК
- YEARFRAC
У формулах можна використовувати також багато інших функцій DAX. Наприклад, багато формул, описаних тут, використовують математичні та тригонометричні функції , такі як MOD і TRUNC, логічні функції , як-от IF, і текстові функції , такі як FORMAT . Докладні відомості про інші функції DAX див. в розділі " Додаткові ресурси " далі в цій статті.
Приклади формул для календарного року
У наведеному нижче прикладі описано формули, які використовуються для створення додаткових стовпців у таблиці дат під назвою CalendarCalendar. Один стовпець під назвою "Дата" вже існує та містить неперервний діапазон дат від 01.01.2010 до 31.12.2016.
Рік
=YEAR([дата])
У цій формулі функція YEAR повертає рік, виходячи зі значення в стовпці Date. Оскільки значення в стовпці "Дата" має тип даних "Дата-час", функція YEAR знає, як повернути з нього рік.
Місяць
=MONTH([дата])
У цій формулі, як і у випадку з функцією YEAR, можна просто скористатися функцією MONTH , щоб повернути значення місяця зі стовпця Date.
Квартал
=INT(([Місяць]+2)/3)
У цій формулі функція INT повертає значення дати у вигляді цілого числа. Аргумент, який ми вказуємо для функції INT, – це значення зі стовпця "Місяць", додайте 2 та поділіть його на 3, щоб отримати наш квартал з 1 по 4.
Назва місяця
=FORMAT([дата];"мммм")
У цій формулі, щоб отримати назву місяця, ми використовуємо функцію FORMAT , яка перетворює числове значення зі стовпця "Дата" на текст. Першим аргументом вказуємо стовпець Date, а потім формат; Ми хочемо, щоб у назві місяця відображалися всі символи, тому ми використовуємо "mmmm". Наш результат виглядає так:
Якщо ми хочемо повернути скорочену назву місяця до трьох букв, ми використаємо "mmm" в аргументі "формат".
День тижня
=FORMAT([дата];"ddd")
У цій формулі ми використовуємо функцію FORMAT, щоб отримати назву дня. Оскільки нам потрібна скорочена назва дня, ми вказуємо "ddd" в аргументі "формат".
Зразок зведеної таблиці
Маючи поля для дат (наприклад, "Рік", "Квартал", "Місяць" тощо), їх можна використати у зведеній таблиці або звіті. Наприклад, на наведеному нижче зображенні показано поле "Обсяг_продажу" з таблиці фактів "Продажі" у форматі "ЗНАЧЕННЯ" та "Рік" і "Квартал" із таблиці вимірів Calendar у рядках (РЯДКИ). Значення SalesAmount узагальнюється для розрізу року та кварталу.
Приклади формул для фінансового року
Фінансовий рік
=IF([Місяць]<= 6;[Рік];[Рік]+1)
У цьому прикладі фінансовий рік починається 1 липня.
Не існує функції, яка могла б видобути фінансовий рік зі значення дати, оскільки дати початку та завершення фінансового року часто відрізняються від дат календарного року. Щоб отримати фінансовий рік, спочатку скористаємось функцією IF , щоб перевірити, чи значення місяця менше або дорівнює 6. У другому аргументі, якщо значення параметра «Місяць» менше або дорівнює 6, повертається значення зі стовпця «Рік». Якщо ні, повертається значення з Year і додається 1.
Ще один спосіб визначити значення місяця кінця фінансового року – створити міру, яка просто визначає місяць. Наприклад, FYE:=6. Після цього можна посилатися на ім'я міри замість номера місяця. Наприклад, =IF([Місяць]<=[FYE];[Рік];[Рік]+1). Це забезпечує більшу гнучкість посилання на місяць кінця фінансового року в кількох різних формулах.
Фінансовий місяць
=IF([Місяць]<=6, 6+[Місяць], [Місяць]- 6)
У цій формулі ми вказуємо, якщо значення параметра [Місяць] менше або дорівнює 6, тоді беремо 6 і додаємо значення з таблиці Місяць, в іншому випадку віднімаємо 6 від значення [Місяць].
Фінансовий квартал
=INT(([FiscalMonth]+2)/3)
Формула, яка використовується для фінансового кварталу, майже така сама, як і для кварталу в нашому календарному році. Різниця полягає в тому, що замість значення [Місяць] указується значення [FiscalMonth].
Свята або особливі дати
Ви можете додати стовпець із датами, у якому певні дати позначено як свята або будь-які інші особливі дати. Наприклад, можна підсумувати підсумки збуту на Новий рік, додавши поле "Свято" до зведеної таблиці як роздільник або фільтр. В інших випадках може знадобитися виключити ці дати з інших стовпців дат або в міру.
Включити свята або особливі дні досить просто. У програмі 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],CalendarCalendar[date])
Розгляньмо цю формулу уважніше.
Функція LOOKUPVALUE використовується, щоб отримати значення зі стовпця "Свята" в таблиці "Свята". У першому аргументі вказуємо стовпець, де буде наше результуюче значення. Ми вказуємо стовпець Holiday у таблиці Holidays , тому що саме це значення ми хочемо повернути.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],CalendarCalendar[date])
Потім укажіть другий аргумент – стовпець пошуку, у якому містяться дати, які потрібно знайти. Стовпець Date у таблиці "Свята" вказуємо так:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],CalendarCalendar[date])
Насамкінець, укажіть стовпець у таблиці Calendar з датами, які потрібно шукати в таблиці "Свята". Це, звичайно ж, стовпець "Дата" в таблиці Calendar.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],CalendarCalendar[date])
Стовпець "Свята" повертатиме назву кожного рядка, який містить значення дати, що збігається з датою в таблиці "Свята".
Настроюваний календар – тринадцять чотиритижневих періодів
Деякі організації, як-от роздрібна торгівля або громадське харчування, часто звітують про різні періоди, наприклад тринадцять чотиритижневих. При тринадцяти чотиритижневому календарі кожен період становить 28 днів; Таким чином, кожен період містить чотири понеділка, чотири вівторка, чотири середи і так далі. Кожен період містить однакову кількість днів, і, як правило, свята припадають на один і той самий період кожного року. Місячні можна почати в будь-який день тижня. Як і у випадку з датами в календарному або фінансовому році, за допомогою формул DAX можна створити додаткові стовпці з настроюваними датами.
У наведених нижче прикладах перший повний період починається в першу неділю фінансового року. У цьому випадку фінансовий рік починається 1.07.
Тиждень
Це значення дає нам номер тижня, починаючи з першого повного тижня фінансового року. У цьому прикладі перший повний тиждень починається в неділю, тому перший повний тиждень першого фінансового року в таблиці Calendar фактично починається 04.07.2010 і продовжується до останнього повного тижня в таблиці Calendar. Хоча це значення не дуже корисне для аналізу, його необхідно обчислити для використання в інших формулах 28-денного періоду.
=INT([дата]-40356)/7)
Розгляньмо цю формулу уважніше.
Спочатку створимо формулу, яка повертає значення зі стовпця Date як ціле число, наприклад:
=INT([дата])
Тоді ми хочемо знайти першу неділю першого фінансового року. Бачимо, що це 04.07.2010.
Тепер відніміть від цього значення 40356 (ціле число для 27.06.2010, останньої неділі попереднього фінансового року), щоб отримати кількість днів від початку днів у таблиці Calendar, наприклад:
=INT([дата]-40356)
Потім розділіть результат на 7 (днів у тижні), як показано нижче.
=INT(([дата]-40356)/7)
Результат має такий вигляд:
Period (крапка)
Період у цьому користувацькому календарі складається з 28 днів і завжди починається в неділю. Цей стовпець повертає номер періоду, який починається з першої неділі першого фінансового року.
=INT(([Тиждень]+3)/4)
Розгляньмо цю формулу уважніше.
Спочатку створимо формулу, яка повертає значення зі стовпця "Тиждень" як ціле число, наприклад:
= INT([Тиждень])
Потім додайте до цього значення 3, наприклад:
=INT([Тиждень]+3)
Потім розділіть результат на 4, наприклад:
=INT(([Тиждень]+3)/4)
Результат має такий вигляд:
Період Фінансовий рік
Це значення повертає фінансовий рік за період.
=INT(([Період]+12)/13)+2008
Розгляньмо цю формулу уважніше.
Спочатку створимо формулу, яка повертає значення з таблиці Period і додає 12:
=([Крапка]+12)
Отриманий результат ділимо на 13, тому що у фінансовому році тринадцять періодів по 28 днів:
=(([Крапка]+12)/13)
Додаємо 2010 рік, тому що це перший рік у таблиці:
=(([Період]+12)/13)+2010
Нарешті, ми скористаємося функцією INT, щоб видалити будь-яку частину результату та повернути ціле число після ділення на 13, наприклад:
= INT(([Період]+12)/13)+2010
Результат має такий вигляд:
Період у фінансовому році
Це значення повертає номер періоду (від 1 до 13), починаючи з першого повного періоду (починається в неділю) у кожному фінансовому році.
=IF(MOD([Крапка];13), MOD([Період];13);13)
Ця формула трохи складніша, тому спочатку ми опишемо її зрозумілою для нас мовою. Ця формула означає: «Поділіть значення [Період] на 13, щоб отримати номер періоду (1–13) у році. Якщо 0, повертається 13.
Спочатку створимо формулу, яка повертає залишок значення від значення «Період» на число 13. Математичні та тригонометричні функції можна використовувати таким чином:
= MOD([Крапка],13)
Це, здебільшого, дає потрібний результат, окрім випадків, коли значення аргументу «період» дорівнює 0, оскільки ці дати не належать до першого фінансового року, як у перші п'ять днів у нашому прикладі Calendar таблиці дат. Ми можемо подбати про це за допомогою функції IF. Якщо наш результат дорівнює 0, ми повертаємо 13, наприклад:
= IF(MOD([Період];13);MOD([Період];13);13)
Результат має такий вигляд:
Зразок зведеної таблиці
На зображенні нижче показано зведену таблицю з полем "Обсяг_продажів" із таблиці фактів "Продажі" у розділі "ЗНАЧЕННЯ" та полями PeriodFiscalYear та "ПеріодInFiscalYear" із таблиці вимірів дат Calendar у рядках. Значення SalesAmount агрегується для контексту за фінансовим роком і 28-денним періодом фінансового року.
Зв’язки
Створивши таблицю дат у моделі даних, щоб почати переглядати дані у зведених таблицях і звітах і щоб агрегувати дані на основі стовпців у таблиці вимірів дати, потрібно створити зв'язок між таблицею фактів із даними про транзакції та таблицею дат.
Оскільки потрібно створити зв'язок на основі дат, слід переконатися, що такий зв'язок створено між стовпцями, значення яких мають тип даних "дата-час".
Для кожного значення дати в таблиці фактів пов'язаний стовпець підстановки в таблиці дат має містити відповідні значення. Наприклад, рядку (запису транзакції) з таблиці фактів "Продажі" зі значенням "15.08.2012 12:00 AM" у стовпці "DateKey" має відповідати значення в пов'язаному стовпці "Date" таблиці дат (під назвою "CalendarCalendar Calendar"). Це одна з найважливіших причин, чому стовпець дат у таблиці дат має містити неперервний діапазон дат, який містить будь-яку можливу дату в таблиці фактів.
Примітка.
Хоча стовпець дати в кожній таблиці має містити дані одного типу (Date), формат кожного стовпця не має значення.
Примітка.
Якщо в надбудові Power Pivot не можна створювати зв'язки між двома таблицями, поля дати можуть не зберігати дату й час з однаковим рівнем точності. Залежно від форматування стовпця значення можуть виглядати однаково, але зберігатися по-різному. Детальніше про роботу з часом.
Примітка.
Уникайте використання цілочисельних сурогатних ключів у стосунках. Коли дані імпортуються з реляційного джерела даних, часто стовпці дати й часу представлено сурогатним ключем, тобто цілочисельним стовпцем, який використовується для представлення унікальної дати. У надбудові Power Pivot не слід створювати зв'язки за допомогою цілочислових ключів дати й часу, а натомість використовувати стовпці, які містять унікальні значення з типом даних дати. Хоча використання сурогатних ключів вважається найкращою практикою в традиційних сховищах даних, цілі ключі не потрібні в надбудові Power Pivot і можуть ускладнити групування значень у зведених таблицях за різними періодами дат.
Якщо під час спроби створити зв'язок з'явиться повідомлення про невідповідність типу, цілком імовірно, що стовпець у таблиці фактів не має типу даних "Дата". Це може статися, коли надбудова Power Pivot не може автоматично перетворити тип даних, відмінний від дати (зазвичай текстовий), на дату. Можна й надалі використовувати стовпець у таблиці фактів, але доведеться перетворювати дані за допомогою формули DAX у новому обчислюваному стовпці. Докладні відомості див. в розділі "Перетворення дат і типів даних текстового типу" на "Дата ".
Множинні зв'язки
У деяких випадках може знадобитися створити кілька зв'язків або кілька таблиць дат. Наприклад, якщо таблиця фактів про збут містить кілька полів дати, наприклад "ДатаКлюч", "Дата_доставки" та "Дата_повернення", усі вони можуть мати зв'язки з полем "Дата" в таблиці дат Calendar, але лише одне з них може бути активним. У цьому випадку, оскільки DateKey представляє дату транзакції, а отже, найважливішу дату, найкраще використовувати її як активний зв'язок. Інші мають неактивні стосунки.
У наведеній нижче зведеній таблиці обчислюється загальний обсяг продажів за фінансовий рік і фінансовий квартал. Міра "Загальний обсяг продажів" із формулою "Загальний обсяг продажів:=SUM([Обсяг продажів])" поміщається в таблицю ЗНАЧЕННЯ, а поля "Фінансовий_рік" і "Фінансовий_квартал" з таблиці дат Calendar поміщаються в рядки таблиці дат.
Ця зведена таблиця працює правильно, тому що потрібно підсумувати загальний обсяг збуту за датою транзакції в ключі DateKey. Показник загальних продажів використовує дати в DateKey і підсумовується за фінансовим роком і фінансовим кварталом, оскільки між стовпцем DateKey в таблиці "Продажі" і стовпцем "Дата" в таблиці дат Calendar встановлено зв'язок.
Неактивні зв'язки
Але що, якщо ми хочемо підсумувати загальний обсяг продажів не за датою транзакції, а за датою відвантаження? Нам потрібен зв'язок між стовпцями "Дата_доставки" таблиці "Збут" і стовпцем "Дата" таблиці Calendar. Якщо такого зв'язку немає, наші агрегації завжди базуються на даті транзакції. Однак, ми можемо мати кілька зв'язків, навіть якщо лише один із них може бути активним, і оскільки дата транзакції найважливіша, він отримує активний зв'язок із таблицею Calendar.
У цьому випадку ShipDate має неактивний зв'язок, тому будь-яка формула міри, створена для агрегації даних на основі дат доставки, має визначати неактивний зв'язок за допомогою функції USERELATIONSHIP .
Наприклад, оскільки між стовпцями "Дата_доставки" таблиці "Збут" і стовпцем "Дата" таблиці Calendar встановлено неактивний зв'язок, можна створити міру, яка підсумовує загальний обсяг продажів за датою відвантаження. Формула має такий вигляд, щоб вказати потрібний зв'язок:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], CalendarCalendar[Date]))
Ця формула має такий вигляд: Обчисліть суму для стовпця SalesAmount, але для фільтрування використовуйте зв'язок між стовпцем ShipDate у таблиці Sales і Date у таблиці Calendar.
Тепер, якщо створити зведену таблицю та помістити міру загального обсягу продажів за датою відвантаження в ЗНАЧЕННЯ, а фінансовий рік і фінансовий квартал у рядки ROWS, ми отримаємо один і той же загальний підсумок, але всі інші суми за фінансовий рік і фінансовий квартал відрізняються, оскільки базуються на даті відвантаження, а не на даті транзакції.
) 
Використання неактивних зв'язків дає змогу використовувати лише одну таблицю дат, але для цього потрібно, щоб будь-які показники (наприклад, "Загальний обсяг продажів за датою відвантаження") посилалися на неактивний зв'язок у формулі. Є й інший варіант – використовувати кілька таблиць дат.
Кілька таблиць дат
Інший спосіб працювати з кількома стовпцями дат у таблиці фактів – створити кілька таблиць дат і створити окремі активні зв'язки між ними. Розгляньмо ще раз приклад із таблицею "Збут". У нас є три стовпці з датами, за якими можна агрегувати дані:
- Ключ дати з датою продажу для кожної транзакції.
- Дата доставки – дата й час, коли продані товари були відправлені клієнту.
- Дата повернення – дата й час отримання одного або кількох повернених товарів.
Пам'ятайте, що поле DateKey з датою транзакції дуже важливе. Більшість агрегацій буде здійснюватися на основі цих дат, тому, безперечно, нам знадобиться зв'язок між ними та стовпцем "Дата" в таблиці Calendar. Щоб не створювати неактивних зв'язків між полями ShipDate і ReturnDate у таблиці Calendar, тому потрібні формули спеціальних мір, можна створити додаткові таблиці дат для дати доставки та дати повернення. Тоді ми можемо створювати активні стосунки між ними.
У цьому прикладі ми створили іншу таблицю дат під назвою ShipCalendar. Це, звісно, також означає створення додаткових стовпців дат. Оскільки ці стовпці дат містяться в іншій таблиці дат, ми хочемо дати їм назви таким чином, щоб вони відрізнялися від тих самих стовпців у таблиці Calendar. Наприклад, ми створили стовпці з назвами "Рік_доставки", "Місяць_доставки", "Квартал_доставки" тощо.
Якщо ми створимо зведену таблицю та помістимо показник загального обсягу продажів у стовпці VALUES, а показники ShipFiscalYear та ShipFiscalQuarter у рядки ROWS, ми отримаємо ті самі результати, що й під час створення неактивного зв'язку та спеціального обчислюваного поля "Загальний обсяг продажів за датою відвантаження".
Кожен з цих підходів вимагає ретельного розгляду. У разі використання кількох зв'язків з однією таблицею дат може виникнути потреба створити спеціальні заходи, які пропускатимуть неактивні зв'язки, за допомогою функції USERELATIONSHIP. З іншого боку, створювати кілька таблиць дат у списку полів непросто, оскільки в моделі даних більше таблиць, знадобиться більше пам'яті. Спробуйте визначити, що вам найкраще підходить.
Властивість «Таблиця дат»
Властивість "Таблиця дат" установлює метадані, необхідні для правильної роботи Time-Intelligence функцій, як-от TOTALYTD, PREVIOUSMONTH і DATESBETWEEN. Коли обчислення виконується за допомогою однієї з цих функцій, обробник формул Power Pivot знає, куди йти, щоб отримати потрібні дати.
Попередження
Якщо цю властивість не задано, міри з використанням функцій DAX Time-Intelligence можуть повертати неправильні результати.
Коли ви налаштовуєте властивість "Таблиця дат", ви вказуєте таблицю дат і стовпець дат із типом даних "Дата" (дата-час).
Інструкції: Установлення властивості "Таблиця дат"
- У вікні PowerPivot виберіть CalendarCalendar table.
- На вкладці "Конструктор " виберіть команду "Позначити як таблицю дати".
- У діалоговому вікні "Позначити як таблицю дати" виберіть стовпець з унікальними значеннями та типом даних "Дата".
Робота з часом
Усі значення дат із типом даних "Дата" в Excel або SQL Server фактично є числоми. Це число включають цифри, які позначають час. У багатьох випадках час кожного рядка – північ. Наприклад, якщо поле DateTimeKey в таблиці фактів "Продаж" має значення, як-от 19.10.2010 12:00:00, це означає, що значення мають денну точність. Якщо значення поля DateTimeKey включають час, наприклад 19.10.2010 8:44:00 AM, це означає, що значення мають хвилинну точність. Значення також можуть бути до рівня точності годинного рівня або навіть секунди. Рівень точності значення часу суттєво вплине на спосіб створення таблиці дат і зв'язків між нею та таблицею фактів.
Вам потрібно визначити, з яким рівнем точності будуть агрегувати дані: за день або за часом. Іншими словами, стовпці таблиці дат (наприклад, "Ранок", "День" або "Година") можна використовувати як поля дати часу в областях "Рядок", "Стовпець" або "Фільтр" зведеної таблиці.
Примітка.
Дні – це найменша одиниця часу, з якою можуть працювати функції часового інтелекту DAX. Якщо не потрібно працювати зі значеннями часу, слід зменшити точність даних, щоб використовувати дні як мінімальну одиницю.
Якщо ви плануєте агрегувати дані до рівня часу, у таблиці дат має бути стовпець дат із часом. Для цього знадобиться стовпець дат з одним рядком на кожну годину або навіть кожну хвилину кожного дня кожного року в проміжку часу. Це пояснюється тим, що для створення зв'язку між стовпцем DateTimeKey в таблиці фактів і стовпцем дати в таблиці дат потрібні збіги значень. Як ви можете собі уявити, якщо включити багато років, з таблиці дат може вийти дуже велика таблиця дат.
Однак у більшості випадків потрібно агрегувати дані лише за день. Іншими словами, такі стовпці, як "Рік", "Місяць", "Тиждень" або "День тижня", потрібно використовувати як поля в областях "Рядок", "Стовпець" або "Фільтр" зведеної таблиці. У цьому випадку стовпець дат у таблиці дат має містити лише один рядок для кожного дня року, як описано вище.
Якщо стовпець дат включає рівень точності в часі, але агрегація буде виконуватися лише на рівні дня, щоб створити зв'язок між таблицями фактів і таблицями дат, може знадобитися змінити таблицю фактів, створивши новий стовпець, у якому значення в стовпці дати скорочуються до значення дня. Іншими словами, потрібно перетворити значення, як-от 19.10.2010 8:44:00 , на 19.10.2010 12:00:00 AM. Можна потім створити зв'язок між цим новим стовпцем і стовпцем дат у таблиці дат, оскільки значення збігаються.
Розглянемо приклад. На цьому зображенні показано стовпець DateTimeKey в таблиці фактів про збут. Дані в цій таблиці агрегуються лише на рівні дня за допомогою стовпців у таблиці дат Calendar, як-от "Рік", "Місяць", "Квартал" тощо. Час, включений у значення, не має значення, а лише фактична дата.
Оскільки нам не потрібно аналізувати ці дані на рівні часу, нам не потрібно, щоб стовпець "Дата" в таблиці дат Calendar включав один рядок для кожної години та кожної хвилини кожного дня кожного року. Отже, стовпець Date у нашій таблиці дат має такий вигляд:
Щоб створити зв'язок між стовпцями DateTimeKey в таблиці Sales і Date в таблиці Calendar, можна створити новий обчислюваний стовпець в таблиці фактів про продаж і за допомогою функції TRUNC скоротити значення дати й часу в стовпці DateTimeKey до значення дати, яке збігається зі значеннями в стовпці Date таблиці Calendar. Наша формула має такий вигляд:
=TRUNC([DateTimeKey];0)
Це дасть нам новий стовпець (який ми назвали DateKey) з датою зі стовпця DateTimeKey і часом 12:00:00 AM для кожного рядка:
Тепер можна створити зв'язок між цим новим стовпцем (DateKey) і стовпцем Date у Calendar таблиці.
Аналогічно, можна створити обчислюваний стовпець у таблиці «Збут», який зменшує точність часу у стовпці DateTimeKey до рівня точності годин. У цьому випадку функція TRUNC не працюватиме, але ми все одно зможемо використати інші функції дати й часу DAX, щоб видобути та повторно об'єднати нове значення з годинною точністю. Формула може бути такою:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Наш новий стовпець виглядає так:
Якщо стовпець "Дата" в таблиці дат має значення з точністю до годин, ми можемо створити зв'язок між ними.
Зручніше використання дат
Багато стовпців дат, які створюються в таблиці дат, потрібні для інших полів, але насправді вони не дуже корисні для аналізу. Наприклад, поле DateKey у таблиці "Продажі", на яку ми посилалися та відображали її в цій статті, дуже важливе, оскільки для кожної транзакції така транзакція записується як така, що відбувається в певні день і час. Проте з точки зору аналізу та звітності він не дуже корисний, оскільки його не можна використовувати як поле рядка, стовпця або фільтра у зведеній таблиці чи звіті.
Аналогічно, у нашому прикладі, стовпець Date у CalendarCalendar таблиці є дуже корисним, критичним по суті, але ви не можете використовувати його як вимір у зведеній таблиці.
Щоб зберегти якомога зручніші таблиці та стовпці в них, а також спростити перехід між списками полів у зведеній таблиці або звіті Power View, важливо приховати непотрібні стовпці в засобах клієнта. Також можна приховати певні таблиці. Наведена вище таблиця "Свята" містить дати свят, важливі для певних стовпців таблиці Calendar, однак стовпці "Дата" та "Свята" в таблиці "Свята" не можна використовувати як поля зведеної таблиці. У новій статті, щоб спростити навігацію в списках полів, можна приховати всю таблицю "Свята".
Ще один важливий аспект у роботі з датами – це правила іменування. У надбудові Power Pivot можна надавати таблиці й стовпцям будь-які імена. Але майте на увазі: особливо якщо ви плануєте надавати спільний доступ до своєї книги іншим користувачам: правильна угода про іменування полегшує визначення таблиць і дат не лише в списках полів, а й у надбудові Power Pivot і формулах DAX.
Додавши таблицю дат до моделі даних, можна почати створювати міри, які допоможуть максимально ефективно використовувати дані. Деякі з них можуть бути настільки ж простими, як підсумувати підсумки збуту за поточний рік, а інші можуть бути складнішими, коли потрібно фільтрувати дані за певним діапазоном унікальних дат. Докладні відомості див. в статті "Міри" у функціях Power Pivot і часового аналізу.
Додаток
Перетворення дат із текстовим типом даних на дату
У деяких випадках таблиця фактів із даними про транзакції може містити дати текстового типу даних. Тобто дата, яка відображається як 2012-12-04T11:47:09, насправді зовсім не є датою або принаймні не є датою того типу, який може зрозуміти надбудова Power Pivot. Насправді це просто текст, який читається як дата. Щоб створити зв'язок між стовпцем дати в таблиці фактів і стовпцем дат у таблиці дат, обидва стовпці мають належати до типу даних "Дата ".
Зазвичай, коли ви намагаєтеся змінити тип даних для стовпця дат із текстовим типом даних на тип даних дати, надбудова Power Pivot може інтерпретувати дати й автоматично перетворити цей тип на дату True. Якщо надбудові Power Pivot не вдасться виконати перетворення типу даних, станеться повідомлення про невідповідність типу.
Проте ви все одно можете перетворити дати на дані зі справжнім типом даних. Ви можете створити новий обчислюваний стовпець і за допомогою формули DAX проаналізувати рік, місяць, день, час тощо з текстових рядків, а потім знову об'єднати їх таким чином, щоб надбудова Power Pivot могла зчитувати їх як справжню дату.
У цьому прикладі ми імпортували таблицю фактів "Продажі" в надбудову Power Pivot. Він містить стовпець DateTime. Значення відображаються таким чином:
Якщо ми подивимося на тип даних у групі "Форматування" на вкладці "Основне" Power Pivot, то побачимо, що це текстовий тип даних.
Не вдалося створити зв'язок між стовпцями DateTime і Date у таблиці дат, оскільки типи даних не збігаються. Якщо ми спробуємо змінити тип даних на Date, то отримаємо помилку невідповідності типу:
У цьому випадку надбудові Power Pivot не вдалося перетворити текстовий тип даних на дату. Можна й надалі використовувати цей стовпець, але для того, щоб надати йому тип даних "Істина", потрібно створити новий стовпець, у якому аналізується текст і створюється заново значення Power Pivot може створити тип даних "Дата".
Пам'ятайте, що з розділу «Робота з часом» вище в цій статті; Якщо точність аналізу не обов'язкова, дати в таблиці фактів повинні перетворюватися на дані з певним рівнем точності. Пам'ятаючи про це, потрібно, щоб значення в нашому новому стовпці мали рівень точності дня (без урахування часу). Ми можемо перетворити значення в стовпці "Дата_час" на тип даних "Дата" та прибрати рівень часу точності за допомогою такої формули:
=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2))
Відобразиться новий стовпець (у цьому випадку з іменем "Дата"). Надбудова Power Pivot навіть визначає значення як дати та автоматично призначає тип даних "Дата".
Якщо потрібно зберегти рівень точності часу, ми просто розширимо формулу, включивши до неї години, хвилини та секунди.
=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, ми можемо створити зв'язок між ним і стовпцем дат у даті.
Додаткові ресурси
Обчислення в надбудові Power Pivot
Короткий посібник. Вивчення основ мови DAX за 30 хвилин