Таблиці дат і їх створення в надбудові Power Pivot для Excel

Таблиці дат у надбудові Power Pivot мають важливе значення для перегляду та обчислення даних із часом. У цій статті наведено глибоке розуміння таблиць дат і способу їх створення в надбудові Power Pivot. Зокрема, у цій статті подано опис:

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

  • Використання надбудови Power Pivot для додавання таблиці дат до моделі даних.

  • Створення нових стовпців дат, таких як рік, місяць і крапка в таблиці дат.

  • Створення зв'язків між таблицями дат і таблицями фактів.

  • Робота з часом.

Ця стаття призначена для користувачів, які не мають нового до надбудови Power Pivot. Тим не менш, важливо вже добре зрозуміти імпорт даних, створення зв'язків і створення обчислюваних стовпців і мір.

У цій статті не описано, як використовувати функції dax Time-Intelligence у формулах. Щоб отримати докладні відомості про те, як створювати заходи за допомогою функцій аналізу часу DAX, у програмі Excel виберіть елемент час розвідки в надбудові Power Pivot.

Примітка.: У надбудові Power Pivot імена "міра" та "обчислюване поле" є синонімами. У цій статті ми використовуємо вимірювану назву. Докладні відомості наведено в статті міри в надбудові Power Pivot.

Зміст

Загальні відомості про таблиці дат

Майже весь аналіз даних передбачає перегляд і порівняння даних за датами та часом. Наприклад, може знадобитися обчислити суму обсягу збуту за минулий фінансовий квартал, а потім порівняти ці підсумки з іншими чвертями або, можливо, потрібно розрахувати залишок на закриття місяця для облікового запису. У кожному з цих випадків ви використовуєте дати як спосіб, щоб групувати та сукупні транзакції збуту або Баланси за певний період часу.

Звіт Power View

Загальний обсяг продажів відповідно до зведеної таблиці за фінансовий квартал

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

Список полів Power View

Список полів Power View

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

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

Таблиця дат із суміжним набором дат

Таблиця дат із неперервними датами

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

Якщо ви звітуватимемо про календарний рік і фінансовий рік, не потрібно створювати окремі таблиці дат. У таблиці з однією датою можна включити стовпці за календарний рік, фінансовий рік, і навіть календар 13 4-тижневого періоду. Важливо, що таблиця дат містить суміжний набір дат для всіх років, що входять до складу.

Додавання таблиці дат до моделі даних

Нижче наведено кілька способів додавання таблиці дат до моделі даних.

  • Імпорт з реляційної бази даних або іншого джерела даних.

  • Створіть таблицю дат у програмі Excel, а потім скопіюйте або створіть посилання на нову таблицю в надбудові Power Pivot.

  • Імпорт із Microsoft Azure ринку.

Розгляньмо кожну з цих найбільш близьких.

Імпорт з реляційної бази даних

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

Майстер імпорту таблиць

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

У більшості випадків вам не потрібно створювати додаткові стовпці, як-от фінансовий рік, тиждень, ім'я місяця тощо, тому що вони вже існують в імпортованій таблиці. Однак у деяких випадках, після того, як таблиця дати імпортується в модель даних, можливо, знадобиться створити додаткові стовпці дати, залежно від того, чи потрібна певна звітність. На щастя, це легко зробити за допомогою DAX. Дізнайтеся більше про те, як створювати поля таблиці дат пізніше. Кожне середовище відрізняється від інших. Якщо ви не впевнені, чи мають ваші джерела даних відповідну дату або таблицю календаря, зверніться до адміністратора бази даних.

Створення таблиці дат у програмі Excel

Таблицю дати можна створити в програмі Excel, а потім скопіювати її до нової таблиці в моделі даних. Це дійсно дуже легко зробити, і це дає вам багато гнучкості.

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

Як створити таблицю дат у програмі Excel і скопіювати його до моделі даних.

  1. У програмі Excel на пустому аркуші в клітинці a1введіть ім'я заголовка стовпця, щоб визначити діапазон дат. Зазвичай це буде щось на кшталт дата, дата й час, або DateKey.

  2. У клітинці a2введіть дату початку. Наприклад, 1/1/2010.

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

    Стовпець дат в Excel

  4. Виділіть усі рядки в стовпці Date (включно з іменем заголовка у клітинці a1).

  5. У групі стилі натисніть кнопку Форматувати як таблицю, а потім виберіть потрібний стиль.

  6. У діалоговому вікні Форматувати як таблицю натисніть кнопку OK.

    Стовпець дат у Power Pivot

  7. Скопіюйте всі рядки, включно з верхнім колонтитулом.

  8. У надбудові Power Pivot на вкладці основне натисніть кнопку Вставити.

  9. У вікні вставлення > ім'я таблиці введіть ім'я, як-от дата або календар. Установіть прапорець використовувати перший рядок як заголовки стовпців , а потім натисніть кнопку OK.

    Попередній перегляд вставлення

    Нова таблиця дат (іменований календар у цьому прикладі) у надбудові Power Pivot має такий вигляд:

    Таблиця дат у Power Pivot

    Примітка.: Ви також можете створити зв'язану таблицю за допомогою компонента додати до моделі даних. Проте ця книга дає змогу без нагальної потреби бути великими, тому що книга містить дві версії таблиці дат; один у програмі Excel і один у надбудові Power Pivot..

Примітка.: Дата Name – це ключове слово в надбудові Power Pivot. Якщо ви називаєте таблицю, яку ви створюєте в полі Дата обчислення, потрібно буде взяти ім'я таблиці з одинарними лапками в будь-яких формулах DAX, які посилаються на нього в аргументі. Усі приклади зображень і формул у цій статті наведено в таблиці дат, створеній в надбудові Power Pivotім'я календаря.

Тепер ви маєте таблицю дат у моделі даних. Ви можете додати нові стовпці дат, як-от рік, місяць і т. д., використовуючи DAX.

Додавання нових стовпців дат до таблиці дат

Таблиця дат з одним стовпцем дат, який має один рядок щодня для кожного року, має важливе значення для визначення всіх дат у діапазоні дат. Це також необхідно для створення зв'язку між таблицею "факт" і таблицею дат. Але це єдиний стовпець дати з одним рядком для кожного дня не стане в нагоді під час аналізу дат у звіті зведеної таблиці або надбудови Power View. У таблиці дат потрібно додати стовпці, які дають змогу об'єднати дані для діапазону або групи дат. Наприклад, може знадобитися обчислити суму обсягу збуту за місяцями або кварталами, або ви можете створити міру, яка обчислює річне зростання за рік. У кожному з цих випадків таблиця дати потребує стовпців year, місяців або кварталу, які дають змогу агрегувати дані за цей період.

Якщо таблицю дати імпортовано з реляційного джерела даних, можливо, він уже містить різні типи стовпців дат. У деяких випадках може знадобитися змінити деякі з цих стовпців або створити додаткові стовпці дат. Це особливо актуально, якщо створити таблицю дат у програмі Excel і скопіювати його до моделі даних. На щастя, створення нових стовпців дат у надбудові Power Pivot дуже легко з функціями дати й часу в Dax.

Порада.: Якщо ви ще не працювали з DAX, це чудове місце, де можна почати навчання з QuickStart: Ознайомтеся з основами DAX за 30 хвилин на Office.com.

Функції дати й часу DAX

Якщо ви коли-небудь працювали з функціями дати й часу в формулах Excel, ймовірно, ви будете знайомі з функціями дати й часу. Хоча ці функції схожі на своїх колег у програмі Excel, існують деякі важливі відмінності.

  • Функції Date і Time для DAX використовують тип даних "Дата й час".

  • Вони можуть приймати значення зі стовпця як аргумент.

  • Їх можна використовувати, щоб повертати та/або маніпулювати значеннями дат.

Ці функції часто використовуються під час створення користувацьких стовпців дат в таблиці дат, тому вони важливі для розуміння. Ми будемо використовувати ряд цих функцій для створення стовпців для року, кварталу, FiscalMonth тощо.

Примітка.: Функції дати й часу в DAX – це не те саме, що й функції часового аналізу. Дізнайтеся більше про час розвідки в надбудові Power Pivot у програмі Excel 2013.

DAX включає такі функції Date і Time:

Існує багато інших функцій DAX, які можна використовувати у формулах. Наприклад, у багатьох формулах, описаних тут, використовуються Математичні та тригонометричні функції , наприклад MOD і TRUNC, логічні функції , наприклад IF, і текстові функції , такі як Формат , щоб отримати докладні відомості про інші функції Dax, перегляньте розділ додаткові ресурси , наведені далі в цій статті.

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

У наведених нижче прикладах описано формули, які використовуються для створення додаткових стовпців у таблиці дат з іменем "Календар". Один стовпець, іменована дата, уже існує, і містить безперервний діапазон дат від 1/1/2010 до 12/31/2016.

Рік

= YEAR ([Дата])

У цій формулі функція year повертає рік від значення в стовпці Date. Оскільки значення в стовпці Date має тип даних "Дата й час", функція YEAR має знати, як повернути рік від нього.

Стовпець "Рік"

Місяць

= МІСЯЦЬ ([Дата])

У цій формулі так само, як і за допомогою функції YEAR, ми можемо просто використовувати функцію місяця , щоб повертати значення місяця зі стовпця дати.

Стовпець "Місяць"

Квартал

= INT (([місяць] + 2)/3)

У цій формулі ми використовуємо функцію Int , щоб повернути значення дати як ціле число. Аргумент, який ми визначили для функції INT, – це значення з стовпця «місяць», додати 2, а потім поділити на 3, щоб отримати квартал від 1 до 4.

Стовпець "Квартал"

Ім'я місяця

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

У цій формулі, щоб отримати ім'я місяця, ми використовуємо функцію FORMAT , щоб перетворити числове значення з стовпця "Дата" на текст. Визначено стовпець Date як перший аргумент, а потім – формат; Ми хочемо, щоб наше ім'я місяця відображали всі символи, тому ми використовуємо "mmmm". Наш результат має такий вигляд:

Стовпець "Назва місяця"

Якщо ми хочемо повернути ім'я місяця скорочено до трьох букв, ми будемо використовувати "МММ" у форматі аргументу.

День тижня

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

У цій формулі ми використовуємо функцію FORMAT, щоб отримати ім'я дня. Оскільки ми просто хочемо, щоб скорочена назва дня, ми вказуємо "DDD" у аргумент Format (формат).

Стовпець "День тижня"
Зразок зведеної таблиці

Якщо у вас є поля для дат, таких як рік, квартал, місяць і т. д., ви можете використовувати їх у зведеній таблиці або звіті. Наприклад, на наведеному нижче зображенні показано поле "сума" з таблиці "відомості про продажі" в рядках "значення", "рік" і "квартал" з таблиці "розмірність календаря" у рядку. Обсяг продажу агрегатується за рік і чверть контексту.

Зразок зведеної таблиці

Приклади формул для фінансового року

Фінансовий рік

= 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)

Формула, яку ми використовуємо для FiscalQuarter, так само, як і для кварталу в нашому календарному році. Різниця лише в тому, що ми вказуємо [FiscalMonth] замість [місяць].

Стовпець "Фінансовий квартал"

Свята або особливі дати

Можливо, потрібно додати стовпець дат, який вказує на певні дати – свята або іншу спеціальну дату. Наприклад, може знадобитися підсумувати підсумки збуту на новий рік, додавши поле свято до зведеної таблиці, як роздільник або фільтр. В інших випадках може знадобитися виключити ці дати з інших стовпців дат або в міру.

У тому числі свята або спеціальні дні дуже просто. У програмі Excel можна створити таблицю, яка містить дати, які потрібно додати. Потім можна скопіювати або використати додати до моделі даних, щоб додати його до моделі даних у вигляді зв'язаної таблиці. У більшості випадків не потрібно створювати зв'язки між таблицею та таблицею календарів. Будь-які формули, які посилаються на нього, можуть повертати значення.

Нижче наведено приклад таблиці, створеної в програмі 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 (свята [свято], свята [Дата], календар [Дата])

Розглянемо цю формулу ще ретельніше.

Щоб отримати значення зі стовпця "Відпустка" у таблиці "свята", скористайтеся функцією LOOKULVALUE. У першому аргументі ми вказуємо стовпець, на який буде вказано наше значення результату. У таблиці " свята " ми вказуємо стовпець "свята" , тому що це значення, яке потрібно повернути.

= LOOKUPVALUE (свята [свято], свята [Дата], календар [Дата])

Після цього вкажіть другий аргумент, стовпець пошуку, який має дати, які потрібно знайти. У таблиці " свята " вказано стовпець " дата ", наприклад:

= LOOKUPVALUE (свята [свято],свята [Дата], календар [Дата])

Зрештою, ми вказуємо стовпець у нашій таблиці календаря , що містить дати, які потрібно знайти в таблиці свят . Це, звичайно, стовпець дати в таблиці календаря .

= LOOKUPVALUE (свята [свято], свята [Дата],календар [Дата])

Стовпець Holiday поверне ім'я свята для кожного рядка з значенням дати, яке збігається з датою в таблиці "свята".

Таблиця "Свята"

Користувацький календар – 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)

Результат має такий вигляд:

Стовпець "Тиждень"

Period (крапка)

Період у цьому настроюваному календарі містить 28 днів, і вона завжди почнеться в неділю. Цей стовпець поверне кількість періодів, починаючи з першої неділі в першому фінансовому році.

= INT (([тиждень] + 3)/4)

Розглянемо цю формулу ще ретельніше.

Спочатку ми створимо формулу, яка повертає значення з стовпця "тиждень" як ціле число, наприклад:

=Int ([тиждень])

Потім додайте 3 до цього значення, наприклад:

= INT ([тиждень]+ 3)

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

= INT (([тиждень] + 3)/4)

Результат має такий вигляд:

Стовпець "Період"

Звітний період фінансового року

Це значення повертає фінансовий рік для певного періоду.

= INT (([крапка] + 12)/13) + 2008

Розглянемо цю формулу ще ретельніше.

Спочатку ми створимо формулу, яка повертає значення з крапки та додає 12:

= ([Крапка] + 12)

Ми ділимо результат на 13, тому що в звітному році є 13 28 денних періодів:

= (([Крапка] + 12)/13)

Додамо 2010, тому що це перший рік у таблиці:

= (([Крапка] + 12)/13)+ 2010

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

=Int(([крапка] + 12)/13)+2010

Результат має такий вигляд:

Стовпець "Фінансовий рік періоду"

Період у FiscalYear

Це значення повертає номер періоду, 1 – 13, починаючи з першого повного періоду (починаючи з неділі) у кожному фінансовому році.

= IF (MOD ([крапка]; 13), MOD ([крапка], 13), 13)

Ця формула є дещо складнішою, тому ми описуємо її першою мовою, яку ми краще розуміємо. Ці стани формули ділять значення від [крапка] до 13, щоб отримати період (1-13) у році. Якщо це число дорівнює 0, поверніться до 13.

Спочатку ми створимо формулу, яка Повертає залишок значення від періоду до 13. Ми можемо використовувати функцію MOD (Математичні та тригонометричні функції), наприклад:

=MOD ([крапка]; 13)

Це, здебільшого, дає нам результат, за винятком випадків, коли значення для періоду дорівнює 0, оскільки ці дати не підпадають під час першого фінансового року, наприклад за перші п'ять днів у таблиці "дата подання календаря". Ми можемо подбати про це за допомогою функції IF. У випадку, якщо наш результат дорівнює 0, ми повертаємо 13, наприклад:

=IF(MOD ([крапка]; 13), MOD ([крапка], 13), 13)

Результат має такий вигляд:

Стовпець "Період фінансового року"

Зразок зведеної таблиці

На зображенні нижче показано зведену таблицю з полем "сума" з таблиці "обсяг збуту" у значеннях "значення", "PeriodFiscalYear" і "PeriodInFiscalYear" з таблиці "вимір дати календаря" в РЯДКАХ. Обсяг продажу агрегатується для контексту за звітний рік і 28-денний період у фінансовому році.

Зразок зведеної таблиці для фінансового року

Зв’язки

Створивши таблицю дати в моделі даних, щоб розпочати перегляд даних у зведених таблицях і звітах, а також до сукупних даних на основі стовпців у таблиці «вимір дати», потрібно створити зв'язок між таблицею даних із даними транзакції та таблицею дат.

Оскільки потрібно створити зв'язок на основі дат, потрібно переконатися, що ви створюєте зв'язок між стовпцями, значення яких мають тип даних дати й часу.

Для кожного значення дати в таблиці, пов'язаного стовпця підстановки в таблиці дат має містити відповідні значення. Наприклад, рядок (запис транзакції) у таблиці "факт збуту" з значенням 8/15/2012 12:00 AM у стовпці DateKey має бути відповідним значенням у стовпці "формат дати" в таблиці "Дата (іменований календар)". Це одна з найважливіших причин, з яких потрібно додати стовпець дати в таблиці дат, що містить суміжний діапазон дат, який містить будь-яку можливу дату в таблиці фактів.

Зв’язки у вікні подання схеми

Примітка.: Хоча стовпець дати в кожній таблиці має бути таким самим типом даних (дата), формат кожного стовпця не має значення..

Примітка.: Якщо надбудові Power Pivot не дасть змогу створювати зв'язки між двома таблицями, поля дати можуть не зберігати дату й час на той самий рівень точності. Залежно від форматування стовпця значення може виглядати так само, але вони будуть зберігатися по-різному. Дізнайтеся більше про роботу з часом.

Примітка.: Уникайте використання цілочислових сурогатних ключів у зв'язках. Коли ви імпортуєте дані з реляційних джерел даних, часто стовпці дати й часу представлено сурогатним ключем, який є цілим стовпцем, який використовується для представлення унікальної дати. У надбудові Power Pivot потрібно уникати створення зв'язків за допомогою числових клавіш дати й часу, а натомість використовувати стовпці, які містять унікальні значення з типом даних дати. Незважаючи на те, що використання сурогатних ключів вважається Найкращою практикою в традиційних складах даних, вони не потрібні в надбудові Power Pivot і можуть ускладнити групування значень у зведених таблицях за різними періодами дат.

Якщо під час спроби створити зв'язок з'являється повідомлення про невідповідність типу, скоріш за все, стовпець у таблиці «дата» не є типом даних дати. Це може статися, якщо в надбудові Power Pivot не можна автоматично перетворити недату (зазвичай це текстовий тип даних) до типу даних дати. Ви все ще можете використовувати стовпець у таблиці фактів, але вам доведеться перетворити дані за допомогою формули DAX в новому обчислюваному стовпці. У статті описано, як перетворити тип даних у текстовому форматі на формат дати.

Кілька зв'язків

У деяких випадках може знадобитися створити кілька зв'язків або створити кілька таблиць дат. Наприклад, якщо в таблиці "факт збуту" є кілька полів дат, наприклад DateKey, дата доставки та дата завершення, усі вони можуть мати зв'язки з полем дати в таблиці дат календаря, але лише один із них може бути активним зв'язком. У цьому випадку, оскільки DateKey представляє дату угоди, і, отже, найважливіша дата, це найкраще слугуватиме активним зв'язком. Решта мають неактивні зв'язки.

Наведена нижче зведена таблиця обчислює загальний обсяг продажів за звітний рік і звітний квартал. Лінійка "загальний обсяг продажів" з загальною формулою збуту: = SUM ([обсяг _ доставки]), ПОМІЩАЄТЬСЯ в значення, а поля FiscalYear і FiscalQuarter з таблиці "Дата календаря" РОЗТАШОВУЮТЬСЯ в рядках.

Зведена таблиця загального обсягу продажів за фінансовим кварталом Список полів зведеної таблиці

Ця зведена таблиця працює належним чином, тому що ми хочемо підсумувати загальний обсяг продажів за датою угодив datekey. Наш загальний міра збуту використовує дати в DateKey і підсумовуються за звітний рік і фіскальним кварталом, тому що в таблиці "Дата й час" використовується зв'язок між DateKey в таблиці "продажі" та "Дата".

Неактивні зв'язки

Але, що робити, якщо ми хочемо, щоб сума нашого сукупного збуту не за датою угоди, а за датою доставки? Нам потрібен зв'язок між стовпцем "Дата доставки" в таблиці "продажі" та "Дата" в таблиці "Календар". Якщо ми не створюємо цей зв'язок, наші агрегації завжди базуються на даті угоди. Однак, ми можемо мати кілька зв'язків, хоча тільки один може бути активним, а тому, що Дата угоди є найважливішою, вона отримує активні зв'язки з таблицею календарів.

У цьому випадку функція ShipDate має неактивний зв'язок, тому будь-які формули, створені для сукупної інформації на основі дат доставки, мають вказувати неактивні зв'язки за допомогою функції Userelationship .

Наприклад, оскільки в таблиці "продажі" та у стовпці "Дата" в таблиці "Календар" відображається неактивний зв'язок між стовпцем "Дата", ми можемо створити вимірювану суму, яка обчислює обсяги збуту за датою доставки. Ми використовуємо формулу Like This, щоб визначити зв'язок, який потрібно використовувати:

Загальний обсяг продажів за датою доставки: = ОБЧИСЛЕННЯ (сума (збут [Продажсума]), USERELATIONSHIP (продажі [Дата доставки]; календар [Дата]))

Ця формула просто заявляє: обчислення суми для обсягу збуту, але фільтрування за допомогою зв'язку між стовпцем "Дата доставки" в таблиці "продажі" та "Дата" в таблиці "Календар".

Тепер, якщо ми створимо зведену таблицю та вкладаємо загальний обсяг продажів за датою доставки за ЗНАЧЕННЯМ, а фінансовий рік та фінансовий квартал у РЯДКАХ, ми бачимо той самий загальний підсумок, але всі інші суми сум для фінансового року та фіскального кварталу відрізняються, оскільки вони базуються на дату доставки, а не на дату угоди.

Зведена таблиця "Total Sales by Ship Date" Список полів зведеної таблиці

Використання неактивних зв'язків дає змогу використовувати лише одну таблицю дат, але вона вимагає, щоб будь-які міри (наприклад, загальний обсяг продажів за датою доставки), посилаються на неактивні зв'язки у формулі. Є ще одна альтернатива, тобто використовувати кілька таблиць дат.

Кілька таблиць дат

Ще один спосіб працювати з кількома стовпцями дати в таблиці фактів – створити кілька таблиць дат і створити окремі активні зв'язки між ними. Розглянемо зразок таблиці збуту ще раз. У нас є три стовпці з датами, які можуть знадобитися для сукупних даних:

  • DateKey з датою продажу для кожної транзакції.

  • Дата доставки – Дата й час, коли продавалися товари були доставлені клієнту.

  • Дата повернення – з датою та часом, коли отримано один або кілька елементів.

Пам'ятайте, що поле DateKey з датою угоди має найважливіше значення. Ми зробимо більшу частину наших агрегації на основі цих дат, тому ми, безумовно, хочемо, щоб зв'язок між ним і стовпцем дати в таблиці календаря. Якщо ми не хочемо створювати неактивні зв'язки між датою доставки та датою повернення та полем дати в таблиці календаря, таким чином, щоб спеціальні формули, можна створити додаткові таблиці дат для доставки дати та дати, що повертаються. Потім ми зможемо створити активні зв'язки між ними.

Зв’язки з кількома таблицями дат у поданні схеми

У цьому прикладі ми створили іншу таблицю дат під назвою ShipCalendar. Це, звичайно, також означає створення додаткових стовпців дат і тому, що ці стовпці дат розташовані в іншій таблиці дат, ми хочемо назвати їх таким чином, щоб вони відрізнятимуться від тих самих стовпців у таблиці календаря. Наприклад, ми створили стовпці з іменем "ShipYear", "ShipMonth", "ShipQuarter" тощо.

Якщо ми створимо зведену таблицю та вкладаємо загальний показник збуту у ЗНАЧЕННЯХ, а також у ShipFiscalYear і Shipfiscalyear у РЯДКАХ, ми бачимо ті самі результати, які ми побачили, коли ми створили неактивні зв'язки та особливий загальний обсяг продажів за обчислюваним полем "Дата доставки".

Зведена таблиця "Total sales" із календарем доставки Список полів таблиці

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

Властивість таблиці дат

Властивість таблиця дат встановлює метадані, необхідні для Time-Intelligence функцій, як-от TOTYD, PREVIOUSMONTH і DATESBETWEEN щоб працювати правильно. Коли обчислення виконується за допомогою однієї з цих функцій, обробник формул Power Pivots знає, куди перейти, щоб отримати потрібні дати.

Попередження!: Якщо ця властивість не встановлено, дії, які використовують функції DAX Time-Intelligence, можуть не повертати неправильні результати.

Якщо встановити властивість таблиця дат, у ньому можна вказати таблицю дат і стовпець дат...

Діалогове вікно "Позначити як таблицю дат"

Настроювання властивості таблиці дат

  1. У вікні PowerPivot виберіть таблицю календаря .

  2. На вкладці Конструктор натисніть кнопку відмітити як таблицю дат.

  3. У діалоговому вікні "відмітити як таблицю дат" виберіть стовпець з унікальними значеннями та типом даних "Дата".

Робота з часом

Усі значення дат із типом даних дати в програмі Excel або SQL Server фактично є числом. До цього числа належать цифри, які посилаються на час. У багатьох випадках цей час для кожного рядка – північ. Наприклад, якщо поле DateTimeKey в таблиці "факт збуту" має значення, як-от 10/19/2010 12:00:00 AM, це означає, що значення мають відповідати рівню точності. Якщо значення поля DateTimeKey мають час, наприклад 10/19/2010 8:44:00 AM, це означає, що значення відповідно до рівня хвилини точності. Значення також можуть бути точність рівня часу, або навіть рівень точності за лічені секунди. Рівень точності в значенні часу матиме істотний вплив на створення таблиці дат і зв'язків між нею та таблицею фактів.

Потрібно визначити, чи потрібно, щоб дані сукупно були зведені на рівень точності або на рівень часу точності. Іншими словами, ви можете використовувати стовпці в таблиці дат, як-от ранок, вдень або година, як поля дати часу в рядках рядка, стовпці або області фільтра.

Примітка.: Дні – це найменший елемент часу, який може працювати з функціями "час". Якщо вам не потрібно працювати зі значеннями часу, потрібно зменшити точність даних, щоб використовувати дні як мінімум.

Якщо ви збираєтесь агрегувати дані на рівні часу, у таблиці дат знадобиться стовпець дат, який буде додано до того часу. По-суті, це знадобиться стовпець дати з одним рядком протягом кожної години, або навіть кожну хвилину, кожен день, протягом кожного року в діапазоні дат. Це пояснюється тим, що для створення зв'язку між стовпцем DateTimeKey в таблиці фактів та в стовпці Дата в таблиці дат потрібно мати відповідні значення. Як ви можете собі уявити, якщо ви включаєте багато років, це можна зробити для дуже великої таблиці дат.

У більшості випадків потрібно об'єднати дані лише на день. Іншими словами, ви будете використовувати стовпці, як-от рік, місяць, тиждень або день тижня як поля в рядках, стовпцях або фільтрах. У цьому випадку стовпець дати в таблиці дат має містити лише один рядок для кожного дня за рік, як описано вище.

Якщо стовпець «дата» містить рівень часу точності, але ви будете сукупно лише на рівень денного, щоб створити зв'язок між таблицею та таблицею дат, можливо, знадобиться змінити таблицю фактів, створивши новий стовпець, який обрізає значення в стовпці дата з значенням день. Іншими словами, Перетворюйте значення, наприклад 10/19/2010 8:44:00AM до 10/19/2010 12:00:00 AM. Потім можна створити зв'язок між цим новим стовпцем і стовпцем дати в таблиці дат, оскільки значення збігаються.

Розглянемо приклад. На цьому зображенні показано стовпець DateTimeKey в таблиці "факт збуту". Усі агрегації для даних у цій таблиці мають бути лише на рівні дня, використовуючи стовпці в таблиці дат календаря, як-от рік, місяць, квартал і т. д. Час, включений до значення, не відповідає лише фактичній даті.

Стовпець DateTimeKey

Оскільки нам не потрібно аналізувати ці дані на рівні часу, у таблиці дат календаря не потрібен стовпець Date, який міститиме один рядок протягом кожної години та кожну хвилину кожного дня щороку. Таким чином, стовпець дати в таблиці дат має такий вигляд:

Стовпець дат у Power Pivot

Щоб створити зв'язок між стовпцем DateTimeKey в таблиці "продажі" та "Дата" в таблиці "Календар", можна створити новий обчислюваний стовпець у таблиці "факт збуту" і скористатися функцією TRUNC , щоб скоротити значення дати й часу в стовпці datetimekey у значенні дати, що відповідає значенням у стовпці "Дата" в таблиці "Календар". Наша формула виглядає так:

= TRUNC ([DateTimeKey]; 0)

Це дає нам новий стовпець (ми назвали DateKey) з датою в стовпці DateTimeKey і час 12:00:00 AM для кожного рядка:

Стовпець DateKey

Тепер можна створити зв'язок між цією новою (DateKey) стовпцем і стовпцем дат у таблиці календаря.

Крім того, ми можемо створити обчислюваний стовпець у таблиці "продажі", що зменшує точність часу в стовпці DateTimeKey на часовий рівень точності. У цьому випадку функція TRUNC не працюватиме, але ми все ще можемо використовувати інші функції дати й часу, щоб витягати та повторно узгоджувати нове значення, щоб отримати часовий рівень точності. Ми можемо використовувати формулу таким чином:

= DATE (YEAR ([DateTimeKey]), місяць ([DateTimeKey]), DAY ([DateTimeKey])) + TIME (HOUR ([DateTimeKey]); 0; 0)

Наш новий стовпець має такий вигляд:

Стовпець DateTimeKey

За умови, що в таблиці дат вказано значення часу, можна створити зв'язок між ними.

Використання дат для підвищення якості

Багато стовпців дати, які ви створюєте в таблиці дат, необхідні для інших полів, але насправді це не все, що корисно в аналізі. Наприклад, поле DateKey в таблиці збуту, що ми посилалися, і відображається в цій статті, має важливе значення, оскільки для кожної транзакції ця угода записується як відбувається на певну дату й час. Але з точки зору аналізу та звітування, це не все, що корисно, оскільки не можна використовувати його як рядок, стовпець або фільтр у таблиці зведених таблиць або звітів.

Аналогічно, у нашому прикладі стовпець Date у таблиці календар дуже корисний, але не можна використовувати як вимір у зведеній таблиці.

Щоб зберегти таблиці та стовпці в них максимально корисними, а також створювати поля звіту зведеної таблиці або надбудови Power View, щоб полегшити навігацію, важливо приховати непотрібні стовпці з інструментів клієнта. Також може знадобитися приховати певні таблиці. Таблиця "свята", що відображається раніше, містить дати свят, важливі для певних стовпців у таблиці календарів, але не можна використовувати стовпці дати й свят в таблиці "свята" як поля у зведеній таблиці. Знову ж таки, щоб полегшити навігацію списком полів, можна приховати всю таблицю свят.

Ще один важливий аспект роботи з датами – це правила іменування. Ви можете назвати таблиці та стовпці в надбудові Power Pivot. Але майте на своєму розумі, особливо якщо ви надаєте спільний доступ до книги іншим користувачам, доцільно визначити, що це дає змогу ідентифікувати таблиці та дати, а не лише в списках полів, а також у надбудові Power Pivot і в формулах DAX.

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

Додаток

Перетворення текстових даних на тип даних дати

У деяких випадках таблиця з даними транзакції може містити дати типу даних "текст". Це дата, що відображається як 2012-12-04T11:47:09 насправді не дата взагалі, або принаймні не тип дати, яка може бути зведена до степеня. Це дійсно просто текст, який читається як дата. Щоб створити зв'язок між стовпцем дат у таблиці фактів, а стовпець дати в таблиці дат, для обох стовпців має бути тип даних Date .

Зазвичай під час спроби змінити тип даних для стовпця дат, які є типом даних "тип даних", у надбудові Power Pivot можна інтерпретувати дати та перетворити її на значення TRUE Date Type автоматично. Якщо в надбудові Power Pivot не вдалося виконати перетворення типу даних, з'являється повідомлення про невідповідність типу.

Проте можна перетворити дати на True-тип даних дати. Ви можете створити новий обчислюваний стовпець і скористатися формулою DAX, щоб проаналізувати рік, місяць, день, час і т. д. із текстових рядків, а потім об'єднати його назад разом, щоб у надбудові Power Pivotзначення можна було прочитати як справжню дату.

У цьому прикладі ми імпортували таблицю з назвою "продажі" в надбудові Power Pivot. У ньому міститься стовпець з іменем дати й часу. Значення відображаються таким чином:

Стовпець DateTime у таблиці фактів.

Якщо ми розглянемо тип даних на вкладці "Основне" в групі "формат Power Pivotна", ми бачимо, що це тип даних "текст".

Тип даних на стрічці

Не вдається створити зв'язок між стовпцем DateTime і стовпцем дати в таблиці дат, оскільки типи даних не збігаються. Якщо ми спробуємо змінити тип даних на дату, ми отримали помилку невідповідність типу:

Помилка про невідповідність

У цьому випадку Power Pivot не зміг перетворити тип даних із тексту на дату. Ми все ще можемо використовувати цей стовпець, але для того, щоб отримати його в істинному типі даних дати, потрібно створити новий стовпець, який обробляє текст і повторно створить його в надбудові Power Pivot може зробити тип даних Date.

Пам'ятайте, що в розділі Робота з часом раніше в цій статті; Якщо ви не хочете, щоб ваш аналіз був на рівні часу від дня точності, потрібно перетворити дати в таблиці "день" на рівень точності. Маючи це на думці, ми хочемо, щоб значення в нашому новому стовпці були на рівні денного точності (за винятком часу). Ми можемо перетворити значення в стовпці "Дата й час" на тип даних дати та видалити рівень точності точного часу, виконавши таку формулу:

= DATE (LEFT ([Дата й час]; 4); MID ([Дата й час]; 6; 2); MID ([Дата й час]; 9; 2))

Це дає нам новий стовпець (у цьому випадку іменована дата). У надбудові Power Pivot навіть виявлено значення, які будуть дати, і автоматично встановлюються типи даних.

Стовпець Date у таблиці фактів

Якщо ми хочемо зберегти часовий рівень точності, ми просто розширюємо формулу, щоб включити години, хвилини та секунди.

= DATE (LEFT ([Дата й час]; 4); MID ([Дата й час]; 6; 2); MID ([Дата й час]; 9; 2)) +

TIME (MID ([Дата й час]; 12; 2), MID ([Дата й час]; 15; 2), MID ([Дата й час]; 18; 2))

Тепер, коли у нас є стовпець дата типу даних дати, ми можемо створити зв'язок між ним і стовпцем дат в даті.

Додаткові ресурси

Дати в надбудові Power Pivot

Обчислення в надбудові Power Pivot

Короткий посібник. Вивчення основ мови DAX за 30 хвилин

виразів аналізу даних

Центр ресурсів для Dax

Потрібна додаткова довідка?

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×