Перейти до основного
Office

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

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

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

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

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

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

  • Дізнайтеся, як створювати зв'язки між таблицями дат і фактів.

  • Як працювати з часом.

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

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

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

Зміст

Докладно про таблиці дат

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

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

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

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

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

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

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

Рік

Місяць

Квартал

Назва місяця

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

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

Фінансовий місяць

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

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

Спеціальний календар – тринадцять чотиритижневих періодів

Тиждень

період

Фінансовий рік періоду

Період FiscalYear

Зв’язки

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

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

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

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

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

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

Додаток

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

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

Докладно про таблиці дат

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

Звіт Power View

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

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

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

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

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

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

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

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

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

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

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

Існує кілька способів, можна додати таблицю дат до моделі даних.

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

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

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

Розгляньмо Детальніше кожний спосіб.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Тип даних дати й часу за допомогою функції DAX дати й часу.

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

  • Користувач може використовуватися для повернення та/або керувати значеннями дат.

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

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

Мова DAX містить такі функції дати й часу:

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

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

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

Рік

=YEAR([Date])

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

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

Місяць

=MONTH([Date])

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

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

Квартал

=Int(([month]+2)/3)

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

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

Назва місяця

=FORMAT([Date],"mmmm")

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

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

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

День тижня

=FORMAT([Date],"DDD")

У цій формулі ми використовуємо функцію формат для отримання імені день. Тому, що ми просто хочете іменем скорочену день, ми вказуємо "ddd" в аргументі формат.

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

Додавши поля дати, наприклад року, квартал, місяць, тощо, можна використовувати їх у звіті зведеної таблиці або. Наприклад, на зображенні нижче показано SalesAmount поле з таблиці фактів продажів значення і рік і квартал з календаря вимір таблиці в РЯДКАХ. SalesAmount агрегацію рік» і «квартал контексту.

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

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

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

= IF([Month]<= 6,[Year],[Year]+1)

У цьому прикладі фінансовий рік починається 1 липня.

Існує немає функції, які можна видобувати фінансового року зі значення дати, оскільки дати початку та завершення для фінансового року, часто відрізняється від календарний рік. Щоб отримати фінансовий рік, ми спочатку Використовуйте функцію IF для перевірки, якщо значення місяця – менше або дорівнює 6. У другий аргумент якщо значення за місяць менше або дорівнює 6, потім повертають значення у стовпці рік. Якщо не вибрано, потім повертає значення за рік і додайте 1.

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

Також можна вказати значення місяця завершення фінансового року – це створення елемента, що просто вказує на місяць. Наприклад, FYE: = 6. Потім можна створити посилання міру ім'я замість номер місяця. Наприклад, = IF([Month]<=[FYE],[Year],[Year]+1). Це забезпечує більша гнучкість під час створення посилання на кінець місяця фінансовий рік у кілька різних формул.

Фінансовий місяць

= IF([Month]<= 6, 6+[Month], [Month]-6)

У цій формулі ми вказуємо, якщо значення [місяці] – менше або дорівнює 6, зробіть 6 і додати значення місяця, в іншому разі віднімання 6 від значення від [місяці].

Стовпець "Фінансовий місяць"

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

=Int(([FiscalMonth]+2)/3)

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

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

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

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

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

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

Дата

Свято

1/1/2010

Новий рік

11/25/2010

День подяки

25/12/2010

Різдвяний

01.01.2011

Новий рік

24/11/2011

День подяки

25/12/2011

Різдвяний

01.01.2012

Новий рік

22.11.2012

День подяки

25/12/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

Новий рік

24/11 або 2016

День подяки

12/25/2016

Різдвяний

У таблиці дат створюємо стовпець із назвою свято та використовуйте таку формулу:

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],Calendar[Date])

Погляньмо на цю формулу дещо уважніше.

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

= LOOKUPVALUE (свята [свята], Holidays[date],Calendar[date])

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

= LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

= LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

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

Спеціальний календар – тринадцять чотиритижневих періодів

У деяких організаціях, як роздрібна торгівля або харчування служби, часто звіт про різні періоди, як-от тринадцять чотиритижневих періодів. За допомогою тринадцять чотиритижневих періодів календаря кожний період може бути 28 днів; тому кожний період містить чотири понеділках, чотири вівторок, середа чотири, і так далі. Кожний період містить таку саму кількість днів, але зазвичай свята будуть підпадають тим же періодом часу щороку. Можна запустити за період на будь-який день тижня. Так само, як дат у календарі або фінансовий рік, використовуючи можна DAX створити додаткові стовпці з датами настроюваний.

У наведених нижче прикладах першого повного періоду запуститься першу неділю фінансовий рік. У цьому випадку фінансовий рік починається 7 та 1.

Тиждень

Це значення дає номер тижня, починаючи з першого повного тижня у фінансовому році. У цьому прикладі першого повного тижня починається в неділю, тому першого повного тижня в першому фінансовий рік у таблиці Calendar фактично починається в 4/7/2010 і триває до останнього повного тижня в календарі таблиці. Під час цього самого значення не все, що пояснюється, як аналізу, варто обчислення для використання в інших 28 днів період формули.

=Int([Date]-40356)/7)

Погляньмо на цю формулу дещо уважніше.

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

=Int([Date])

Потім ми хочемо знайдіть перший неділю за перший фінансовий рік. Ми побачимо, що 7 і 4/2010.

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

Тепер відняти 40356 (це ціле число, яке 6, 27, 2010, останню неділю попереднього фінансового року) значення отримати кількість днів з початку днів у наші таблиця календаря, таким чином:

= INT([date]-40356)

Потім ділимо результат на 7 (днів тижня), ось так:

=Int(([Date]-40356)/7)

Результат виглядає наступним чином:

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

період

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

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

Погляньмо на цю формулу дещо уважніше.

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

=INT([Week])

Потім додайте 3 за цим значенням, таким чином:

= INT([Week]+3)

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

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

Результат виглядає наступним чином:

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

Фінансовий рік періоду

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

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

Погляньмо на цю формулу дещо уважніше.

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

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

Ми ділимо результат на 13, оскільки у фінансовому році тринадцять періодів по 28 днів:

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

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

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

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

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

Результат виглядає наступним чином:

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

Період FiscalYear

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

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

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

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

=MOD([Period],13)

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

=IF(MOD([Period],13), MOD ([період], 13), 13)

Результат виглядає наступним чином:

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

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

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

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

Зв’язки

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

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

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

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

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

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

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

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

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

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

Нижче зведеної таблиці обчислює загальний обсяг продажів за фінансовий рік» і «фінансовий квартал. Міра імені загального обсягу продажів, з формулою, Загальна Sales:=SUM([SalesAmount]), розміщується в значення та FiscalYear та FiscalQuarter поля з таблиці дат календаря розміщуються в РЯДКАХ.

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

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

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

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

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

Наприклад, оскільки неактивні зв'язок між ShipDate стовпця в таблиці Продажі "а стовпець Date у таблиці Calendar, можна створити міра, яка підсумовує загальний обсяг продажів за датою доставки. Щоб визначити зв'язок для використання використовує таку формулу:

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP (збут [Дата], Calendar[Date]))

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

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

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

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

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

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

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

  • ShipDate із датою та часом, коли продані товари були доставлені клієнту.

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

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

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

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

Якщо ми створення наші зведеної таблиці та розміщення наша загальний обсяг продажів міра значення і ShipFiscalYear і ShipFiscalQuarter на РЯДКИ, ми побачимо той же результат, що ми бачили, коли ми створено на неактивний зв'язку та спеціальні Total Sales by Ship Date обчислюване поле.

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

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

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

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

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

Під час настроювання властивості таблиці дат, указаної таблиці дат і стовпець дат зі значеннями типу даних Date (datetime) в ньому.

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

Як: настроювання властивості таблиці дат

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

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

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

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

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

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

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

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

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

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

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

Стовпець DateTimeKey

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

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

Створення зв'язку між DateTimeKey стовпець у таблиці Продажі "а стовпець Date у таблиці Calendar, ми можемо створити новий обчислюваний стовпець у таблиці фактів продажів і за допомогою функції TRUNC скорочуйте значення дати й часу на DateTimeKey стовпці в значення дати, яке відповідає значення в стовпці Date у таблиці Calendar. Наша формула має такий вигляд:

=TRUNC([DateTimeKey],0)

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

Стовпець DateKey

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

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

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey])) + часу (HOUR([DateTimeKey]), 0, 0)

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

Стовпець DateTimeKey

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

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

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

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

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

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

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

Додаток

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

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

Зазвичай під час спроби змінити тип даних у стовпці з датами, які текстовий тип даних до типу даних date, Power Pivot можна інтерпретувати дати та його перетворення на тип даних значення true, дата автоматично. Якщо надбудову Power Pivot не перетворення типу даних, ви отримаєте помилку невідповідність типу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Time(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

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

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

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

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

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

Довідник із виразів аналізу даних

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

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

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

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

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

×