Таблиците с дати в 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 г. Всяка година в таблицата с дати трябва да съдържа всички дни за всяка година. Ако редовно обновявате данните си с по-нови данни, може да искате да изпълните крайната дата до една-две години, така че да не се налага да актуализирате таблицата с дати, докато минава времето.
Date table with a contiguous set of dates
Ако се отчитате за финансова година, можете да създадете таблица с дати с последователен набор от дати за всяка финансова година. Например ако финансовата година започва на 1 март и имате данни за финансови години 2010 до текущата дата (например във ФГ 2013), можете да създадете таблица с дати, която започва на 1.3.2009 г. и включва поне всеки ден във всяка финансова година до последната дата през финансовата 2013 година.
Ако ще се отчитате както за календарната, така и за финансовата година, не е необходимо да създавате отделни таблици с дати. Една таблица с дати може да включва колони за календарна година, финансова година и дори тринадесет календар за четириседмичен период. Важното е, че вашата таблица с дати съдържа последователен набор от дати за всички години, които са включени.
Добавяне на таблица с дати към модела на данни
Има няколко начина, по които можете да добавите таблица с дати към вашия модел на данни:
-
Импортиране от релационна база данни или друг източник на данни.
-
Създаване на съдържание таблица с дати в Excel и след това копирайте или създайте връзка към нова таблица в Power Pivot.
-
Импортиране от Microsoft Azure пазара.
Нека разгледаме всяко от тези по-отблизо.
Импортиране от релационна база данни
Ако импортирате някои или всички ваши данни от склад за данни или друг тип релационни бази данни, вероятно вече има таблица с дати и релации между нея и останалата част от данните, които импортирате. Датите и форматът вероятно ще съответстват на датите във вашите данни за факти, а датите вероятно започват добре в миналото и отиват далеч в бъдещето. Таблицата с дати, която искате да импортирате, може да е много голяма и да съдържа диапазон от дати извън това, което ще трябва да включите във вашия модел на данни. Можете да използвате разширените функции за филтриране на съветника за импортиране на таблици на Power Pivot, за да изберете избирателно само датите и конкретните колони, от които наистина имате нужда. Това може значително да намали размера на работната книга и да подобри производителността.
Съветник за импортиране на таблици
В повечето случаи няма нужда да създавате допълнителни колони, като "Финансова година", "Седмица", "Име на месец" и т.н., тъй като те вече ще съществуват в импортираната таблица. В някои случаи обаче, след като таблицата с дати е импортирана във вашия модел на данни, може да се наложи да създадете допълнителни колони за дата в зависимост от конкретната нужда от отчитане. За щастие, това е лесно да се направи с помощта на DAX. По-късно ще научите повече за създаването на полета на таблица с дати. Всяка среда е различна. Ако не сте сигурни дали вашите източници на данни имат свързана дата или таблица с календари, се обърнете към администратора на базата данни.
Създаване на съдържание таблица с дати в Excel
Можете да създадете таблица с дати в Excel и след това да я копирате в нова таблица в модела на данни. Това е наистина много лесно да се направи и ви дава много гъвкавост.
Когато създавате таблица с дати в Excel, започвате с една колона с непрекъснат диапазон от дати. След това можете да създадете допълнителни колони, като например Година, Тримесечие, Месец, Финансова година, Период и т.н. в работния лист на Excel, като използвате формули на Excel, или след като копирате таблицата в модела на данни, можете да ги създадете като изчисляеми колони. Създаването на допълнителни колони за дата в Power Pivot е описано в раздела Добавяне на нови колони за дата към таблицата с дати по-нататък в тази статия.
Как да: Създаване на съдържание таблица с дати в Excel и да я копирате в модела на данни
-
В Excel, в празен работен лист, в клетка A1 въведете име на заглавка на колона, за да идентифицирате диапазон от дати. Обикновено това ще бъденещо като Date, DateTime или DateKey.
-
В клетка A2 въведете начална дата. Например 1.1.2010 г.
-
Щракнете върху манипулатора за попълване и го плъзнете надолу до номер на ред, който включва крайна дата. Например 31.12.2016 г.
-
Изберете всички редове в колоната "Дата" (включително името на заглавката в клетка A1).
-
В групата Стилове щракнете върху Форматиране като таблица и след това изберете стил.
-
В диалоговия прозорец Форматиране като таблица щракнете върху OK.
-
Копирайте всички редове, включително горния колонтитул.
-
В Power Pivot, в раздела Начало щракнете върху Постави.
-
В Поставяне: визуализация > Име на таблица въведете име, като например Дата или Календар. Оставете Отметнато Използвай първия ред като заглавкина колони и след това щракнете върху OK.
Новата таблица с дати (наречена "Календар" в този пример) в Power Pivot изглежда така:
Забележка: Можете също да създадете свързана таблица с помощта на Добавяне към модела на данни. Това обаче прави работната ви книга ненужно голяма, защото работната книга има две версии на таблицата с дати; един в Excel и един в Power Pivot..
Забележка: Датата на името е ключова дума в Power Pivot. Ако наименуване на таблицата, която създавате в "Дата на Power Pivot", ще трябва да оградите името на таблицата с единични кавички във всички DAX формули, които препращат към нея в аргумент. Всички примерни изображения и формули в тази статия се отнасят за таблица с дати, създадена в Power Pivot с име "Календар".
Сега имате таблица с дати във вашия модел на данни. Можете да добавите нови колони за дата, като например Година, Месец и т.н., с помощта на DAX.
Добавяне на нови колони за дата към таблицата с дати
Таблица с дати с една колона с дати, която има по един ред за всеки ден за всяка година, е важна за дефинирането на всички дати в даден диапазон от дати. Също така е необходимо за създаване на релация между таблицата с факти и таблицата с дати. Но тази единична колона с дата с по един ред за всеки ден не е полезна, когато анализирате по дати в обобщена таблица или отчет на Power View. Искате вашата таблица с дати да включва колони, които да ви помогнат да агрегирате данните си за диапазон или група от дати. Може например да искате да сумирате сумите на продажбите по месец или тримесечие или да създадете мярка, която изчислява годишното нарастване. Във всеки от тези случаи таблицата с дати се нуждае от колони за година, месец или тримесечие, които ви позволяват да агрегирате данните си за този период.
Ако сте импортирали таблицата с дати от релален източник на данни, тя може вече да включва различните типове колони с дати, които искате. В някои случаи може да поискате да промените някои от тези колони или да създадете допълнителни колони за дата. Това е особено вярно, ако създадете собствена таблица с дати в Excel и я копирате в модела на данни. За щастие създаването на нови колони за дата в Power Pivot е доста лесно с функциите за дата и час в DAX.
Съвет: Ако все още не сте работили с DAX, чудесно място за започване на обучение е с QuickStart: Научете основите на DAX за 30 минути на Office.com.
Функции за дата и час на DAX
Ако някога сте работили с функции за дата и час във формули на Excel, вероятно ще сте запознати с функциите за дата и час. Въпреки че тези функции са подобни на аналогичните им функции в Excel, има някои важни разлики:
-
Функциите за дата и час на DAX използват данни от тип datetime.
-
Те могат да приемат стойностите от колона като аргумент.
-
Те могат да се използват за връщане и/или манипулиране на стойности за дати.
Тези функции често се използват при създаване на колони за дата по избор в таблица с дати, така че те са важни за разбиране. Ще използваме няколко от тези функции, за да създадем колони за Година, Тримесечие, ФинансовМесец и т.н.
Забележка: Функциите за дата и час в DAX не са същите като функциите за интелигентно време. Научете повече за интелигентността на времето в Power Pivot в Excel 2013.
DAX включва следните функции за дата и час:
Има много други DAX функции, които можете да използвате и във вашите формули. Например много от формулите, описани тук, използват математически и тригонометрични функции , като MOD и TRUNC, логически функции , като например IF, и текстови функции като FORMAT За повече информация относно други функции на DAX вж. раздела "Допълнителни ресурси " по-нататък в тази статия.
Примери за формули за календарна година
Следващите примери описват формулите, използвани за създаване на допълнителни колони в таблица с дати с име "Календар". Една колона, наречена "Дата", вече съществува и съдържа непрекъснат диапазон от дати от 1.1.2010 г. до 31.12.2016 г.
Година
=YEAR([дата])
В тази формула функцията YEAR връща годината от стойността в колоната "Дата". Тъй като стойността в колоната "Дата" е от тип данни за дата и час, функцията YEAR знае как да върне годината от нея.
Месец
=MONTH([дата])
В тази формула, подобно на функцията YEAR, можем просто да използваме функцията MONTH , за да се върне стойност на месец от колоната "Дата".
Тримесечие
=INT(([Месец]+2)/3)
В тази формула използваме функцията INT , за да се върне стойност на дата като цяло число. Аргументът, който зададем за функцията INT, е стойността от колоната Месец, добавете 2 и след това го разделете на 3, за да получите нашето тримесечие– от 1 до 4.
Име на месеца
=FORMAT([дата];"мммм")
В тази формула, за да получим името на месеца, използваме функцията FORMAT , за да преобразуваме числова стойност от колоната "Дата" в текст. Задаваме колоната "Дата" като първи аргумент и след това формата; искаме името на нашия месец да показва всички знаци, така че използваме "мммм". Нашият резултат изглежда така:
Ако искаме да върнем съкратеното име на месеца с три букви, ще използваме "mmm" в аргумента "формат".
Ден от седмицата
=FORMAT([дата];"ддд")
В тази формула използваме функцията FORMAT, за да получим името на деня. Тъй като искаме само съкратено име на ден, в аргумента "формат" указваме "ddd".
Примерна обобщена таблица
След като имате полета за дати, като например Година, Тримесечие, Месец и т.н., можете да ги използвате в обобщена таблица или отчет. Например изображението по-долу показва полето SalesAmount от таблицата с факти "Продажби" в VALUES и Year и Quarter от таблицата с измерение "Календар" в ROWS. SalesAmount се агрегира за контекста на годината и тримесечието.
Примери за формули за финансова година
Финансова година
=IF([Месец]<= 6;[Година];[Година]+1)
В този пример финансовата година започва на 1 юли.
Няма функция, която да може да извлече финансова година от стойност на дата, защото началната и крайната дата за дадена финансова година често се различават от тези на календарната година. За да получим финансовата година, първо използваме функция IF , за да проверим дали стойността за Месец е по-малка или равна на 6. Във втория аргумент, ако стойността за Месец е по-малка или равна на 6, връща стойността от колоната Година. Ако не, тогава се връща стойността от Year и се добавя 1.
Друг начин да зададете стойност на крайния месец за финансовата година е да създадете мярка, която просто задава месеца. Например FYE:=6. След това можете да посочите името на мярката вместо номера на месеца. Например =IF([Месец]<=[ФЕЕ];[Година];[Година]+1). Това предоставя по-голяма гъвкавост при препращане към крайния месец на финансовата година в няколко различни формули.
Финансов месец
=IF([Месец]<= 6; 6+[Месец], [Месец]- 6)
В тази формула задаваме дали стойността за [Месец] е по-малка или равна на 6, вземете 6 и добавете стойността от Месец, в противен случай извадете 6 от стойността от [Месец].
Финансово тримесечие
=INT(([FiscalMonth]+2)/3)
Формулата, която използваме за "Фискален квартет", е почти същата, както за "Тримесечие" през календарната ни година. Единствената разлика е, че указваме [FiscalMonth] вместо [Месец].
Празници или специални дати
Може да искате да включите колона с дати, която показва, че определени дати са празници или друга специална дата. Може например да искате да сумирате общите суми на продажбите за деня "Нови години", като добавите поле "Празник" към обобщена таблица, като сегментатор или филтър. В други случаи може да поискате да изключите тези дати от други колони за дата или в мярка.
Включително празници или специални дни е доста проста. Можете да създадете таблица в Excel с датите, които искате да включите. След това можете да копирате или използвате "Добавяне към модела на данни", за да го добавите към модела на данни като свързана таблица. В повечето случаи не е необходимо да се създава релация между таблицата и таблицата "Календар". Всички формули, препращащи към нея, могат да използват функцията LOOKUPVALUE за връщане на стойности.
По-долу е даден пример за таблица, създадена в Excel, която включва празници за добавяне към таблицата с дати:
|
Дата |
Празник |
|---|---|
|
1/1/2010 |
Нови години |
|
11/25/2010 |
Благодарността |
|
12/25/2010 |
Коледа |
|
1.01.2011 |
Нови години |
|
11/24/2011 |
Благодарността |
|
12/25/2011 |
Коледа |
|
1.01.2012 |
Нови години |
|
22.11.2012 |
Благодарността |
|
12/25/2012 |
Коледа |
|
1/1/2013 |
Нови години |
|
11/28/2013 |
Благодарността |
|
12/25/2013 |
Коледа |
|
11/27/2014 |
Благодарността |
|
12/25/2014 |
Коледа |
|
1.1.2014 г. |
Нови години |
|
11/27/2014 |
Благодарността |
|
12/25/2014 |
Коледа |
|
1/1/2015 |
Нови години |
|
11/26/2014 |
Благодарността |
|
12/25/2015 |
Коледа |
|
1.01.2016 |
Нови години |
|
11/24/2016 |
Благодарността |
|
12/25/2016 |
Коледа |
В таблицата с дати създаваме колона с име "Празник " и използваме формула като тази:
=LOOKUPVALUE(Празници[Празник];Празници[дата];Календар[дата])
Нека разгледаме тази формула по-внимателно.
Използваме функцията LOOKUPVALUE, за да получим стойности от колоната "Празници" в таблицата "Празници". В първия аргумент задаваме колоната, където ще бъде нашата стойност на резултата. Задаваме колоната "Празници " в таблицата " Празници ", защото това е стойността, която искаме да се върне.
=LOOKUPVALUE(Празници[Празник];Празници[дата];Календар[дата])
След това задаваме втория аргумент – колоната за търсене, която съдържа датите, които искаме да потърсим. Задаваме колоната "Дата " в таблицата "Празници ", ето така:
=LOOKUPVALUE(Празници[Празник];Празници[дата];Календар[дата])
И накрая, задаваме колоната в нашата таблица на календара , която съдържа датите, които искаме да търсим в таблицата "Празници ". Това, разбира се, е колоната "Дата " в таблицата "Календар" .
=LOOKUPVALUE(Празници[Празник];Празници[дата];Календар[дата])
Колоната "Празник" ще върне името на празника за всеки ред, който има стойност за дата, съответстваща на дата в таблицата "Празници".
Календар по избор – тринадесет периода от четири седмици
Някои организации, като например продажби на дребно или хранителни услуги, често докладват за различни периоди, като например тринадесет четириседмичните периоди. С тринадесет четириседмичните дни календар, всеки период е 28 дни; Следователно всеки период съдържа четири понеделника, четири вторника, четири сряда и т.н. Всеки период съдържа един и същ брой дни и обикновено празниците ще попадат в един и същ период всяка година. Можете да изберете да започнете период всеки ден от седмицата. Точно както с дати в календарна или финансова година, можете да използвате DAX, за да създадете допълнителни колони с дати по избор.
В примерите по-долу първият пълен период започва от първата неделя на финансовата година. В този случай финансовата година започва на 7/1.
Седмица
Тази стойност ни дава номера на седмицата, започващ с първата пълна седмица от финансовата година. В този пример първата пълна седмица започва в неделя, така че първата пълна седмица от първата финансова година в таблицата "Календар" всъщност започва на 4.7.2010 г. и продължава през последната пълна седмица в таблицата "Календар". Макар че самата тази стойност не е всичко, което е полезно при анализа, е необходимо да се изчисли за използване във формули за други 28 дни.
=INT([дата]-40356)/7)
Нека разгледаме тази формула по-внимателно.
Първо, създаваме формула, която връща стойности от колоната "Дата" като цяло число, ето така:
=INT([дата])
След това искаме да потърсим първата неделя в първата финансова година. Виждаме, че е 04.07.2010 г.
Сега извадете 40 356 (което е цяло число за 27.6.2010 г., последната неделя от предишната финансова година) от тази стойност, за да получите броя дни от началото на дните в нашата таблица с календари, ето така:
=INT([дата]-40356)
След това разделете резултата на 7 (дни в седмица), ето така:
=INT(([дата]-40356)/7)
Резултатът изглежда по следния начин:
Точка
Периодът в този календар по избор съдържа 28 дни и той винаги ще започва в неделя. Тази колона ще върне номера на периода, започващ от първата неделя в първата финансова година.
=INT(([Седмица]+3)/4)
Нека разгледаме тази формула по-внимателно.
Първо, създаваме формула, която връща стойност от колоната "Седмица" като цяло число, ето така:
=INT([Седмица])
След това добавете 3 към тази стойност, ето така:
=INT([Седмица]+3)
След това разделете резултата на 4, ето така:
=INT(([Седмица]+3)/4)
Резултатът изглежда по следния начин:
Период финансова година
Тази стойност връща финансовата година за период.
=INT(([Период]+12)/13)+2008
Нека разгледаме тази формула по-внимателно.
Първо, създаваме формула, която връща стойност от "Период" и добавя 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 (математически и тригонометрични функции) по следния начин:
=MOD([Период];13)
Това в по-голямата си част ни дава желания резултат, с изключение на мястото, където стойността за "Период" е 0, защото тези дати не попадат в рамките на първата финансова година, като например през първите пет дни на нашия пример таблица с дати в календара. Можем да се погрижим за това с функция IF. В случай че резултатът ни е 0, връщаме 13, ето така:
=IF(MOD([Период];13);MOD([Период];13);13)
Резултатът изглежда по следния начин:
Примерна обобщена таблица
Изображението по-долу показва обобщена таблица с полето SalesAmount от таблицата с факти "Продажби" в VALUES и полета periodFiscalYear и PeriodInFiscalYear от таблицата за размерност "Календар" в ROWS. SalesAmount се агрегира за контекста по финансова година и 28-дневен период през финансовата година.
Релации
След като сте създали таблица с дати във вашия модел на данни, за да започнете да преглеждате данните си в обобщени таблици и отчети и да агрегирате данни въз основа на колоните в таблицата с размерности за дата, трябва да създадете релация между таблицата с факти с данните за транзакцията и таблицата с дати.
Тъй като трябва да създадете релация на базата на дати, ще искате да създадете тази релация между колони, чиито стойности са от тип данни за дата и час (дата).
За всяка стойност на дата в таблицата с факти свързаната справочна колона в таблицата с дати трябва да съдържа съвпадащи стойности. Например ред (запис на транзакция) в таблицата с факти "Продажби" със стойност 15.8.2012 г. 12:00 ч. в колоната DateKey трябва да има съответстваща стойност в свързаната колона "Дата" в таблицата за дата (с име "Календар"). Това е една от най-важните причини, поради които искате колоната за дата в таблицата с дати да съдържа непрекъснат диапазон от дати, който включва всяка възможна дата в таблицата с факти.
Забележка: Докато колоната за дата във всяка таблица трябва да бъде от един и същ тип данни (Дата), форматът на всяка колона няма значение..
Забележка: Ако Power Pivot не ви позволява да създавате релации между двете таблици, полетата за дата може да не съхраняват датата и часа на едно и също ниво на точност. В зависимост от форматирането на колоните стойностите може да изглеждат по същия начин, но да се съхраняват по различен начин. Прочетете повече за работата с времето.
Забележка: Избягвайте използването на целочислени заместващи ключове в релации. Когато импортирате данни от релационни източници на данни, често колоните за дата и час се представят с заместващ ключ, който е цяла колона, използвана за представяне на уникална дата. В Power Pivot трябва да избягвате създаването на релации с помощта на цели клавиши за дата/час, а вместо това да използвате колони, съдържащи уникални стойности с тип данни за дата. Въпреки че използването на заместващи ключове се счита за най-добра практика в традиционните складове за данни, целочисловите ключове не са необходими в Power Pivot и могат да затруднят групирането на стойности в обобщени таблици по различни периоди от дати.
Ако получите грешка "Несъответствие на тип" при опит за създаване на релация, това вероятно се дължи на факта, че колоната в таблицата с факти не е от тип данни "Дата". Това може да се случи, когато Power Pivot не може автоматично да конвертира не-дата (обикновено текстов тип данни) в тип данни за дата. Все още можете да използвате колоната в таблицата с факти, но ще трябва да преобразувате данните с DAX формула в нова изчисляема колона. Вижте Преобразуване на дати от текстов тип данни в тип данни за дата по-нататък в допълнението.
Множество релации
В някои случаи може да е необходимо да създадете няколко релации или да създадете няколко таблици с дати. Ако например в таблицата с данни "Продажби" има няколко полета за дата, например DateKey, ShipDate и ReturnDate, те могат да имат релации към полето "Дата" в таблицата "Дата на календара", но само едно от тях може да бъде активна релация. В този случай, тъй като DateKey представлява датата на транзакцията и следователно най-важната дата, това ще служи най-добре като активна връзка. Другите имат неактивни релации.
Следващата обобщена таблица изчислява общите продажби по финансова година и финансово тримесечие. Мярка с име Total Sales, с формулата Total Sales:=SUM([SalesAmount]), се поставя в VALUES, а полетата FiscalYear и FiscalQuarter от таблицата "Календар" се поставят в ROWS.
Тази обобщена таблица с права работи правилно, защото искаме да сумираме нашите общи продажби до датата на транзакцията в DateKey. Нашата мярка за общи продажби използва датите в DateKey и се сумира от финансовата година и финансово тримесечие, защото има релация между DateKey в таблицата Sales и колоната Date в таблицата Calendar date.
Неактивни релации
Но какво ще стане, ако искаме да сумираме нашите общи продажби не по дата на транзакция, а по дата на изпращане? Нуждаем се от релация между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Календар". Ако не създадем тази релация, нашите агрегирания винаги се базират на датата на транзакцията. Можем обаче да имаме няколко релации, въпреки че само една може да бъде активна и тъй като датата на транзакцията е най-важната, тя получава активната релация с таблицата "Календар".
В този случай "Дата на експедиране" има неактивна релация, така че всяка формула за мярка, създадена за агрегиране на данни на базата на датите на изпращане, трябва да указва неактивната връзка с помощта на функцията USERELATIONSHIP .
Например тъй като има неактивна релация между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Календар", можем да създадем мярка, която сумира общите продажби по дата на доставка. Използваме формула като тази, за да укажем релацията, която да се използва:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Тази формула просто гласи: Изчисляване на сума за SalesAmount, но филтриране с помощта на релацията между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Календар".
Сега, ако създадем обобщена таблица и поставим мярката Total Sales by Ship Date в VALUES и Fiscal Year и Fiscal Quarter в ROWS, виждаме една и съща обща сума, но всички други суми за финансовата година и финансово тримесечие са различни, тъй като те се базират на датата на доставка, а не на датата на транзакцията.
Използването на неактивни релации ви позволява да използвате само една таблица с дати, но изисква всички мерки (като Total Sales by Ship Date) да препращат към неактивната релация във формулата. Има и друга алтернатива, т.е. да използвате няколко таблици с дати.
Множество таблици с дати
Друг начин да работите с няколко колони за дата в таблицата с факти е да създадете няколко таблици с дати и да създадете отделни активни релации между тях. Нека да разгледаме отново нашия пример за таблицата "Продажби". Имаме три колони с дати, по които може да искаме да агрегираме данни:
-
DateKey с датата на продажбата за всяка транзакция.
-
Дата на експедиране – с датата и часа, когато продадените артикули са експедирани на клиента.
-
Дата на връщане – с датата и часа на получаване на един или повече върнати елементи.
Не забравяйте, че полето DateKey с датата на транзакцията е най-важно. Ще извършим повечето от нашите агрегирания въз основа на тези дати, така че със сигурност ще искаме релация между нея и колоната "Дата" в таблицата "Календар". Ако не искаме да създаваме неактивни релации между "Дата на експедиране" и "Дата на връщане" и полето "Дата" в таблицата "Календар", изисквайки по този начин формули за специални мерки, можем да създадем допълнителни таблици за дата на експедиране и дата на връщане. След това можем да създадем активни релации между тях.
В този пример създадохме друга таблица с дати с име ShipCalendar. Това, разбира се, означава също да създадете допълнителни колони за дата и тъй като тези колони за дата са в друга таблица с дати, искаме да ги наименуване по начин, който ги различава от същите колони в таблицата "Календар". Например създадохме колони с име ShipYear, ShipMonth, ShipQuarter и т.н.
Ако създадем нашата обобщена таблица и поставим нашата мярка за общи продажби в VALUES и ShipFiscalYear и ShipFiscalQuarter в РЕДОВЕ, ще видим същите резултати, които видяхме, когато създадохме неактивна релация и специално изчисляемо поле Total Sales by Ship Date.
Всеки от тези подходи изисква внимателно обмисляне. Когато използвате няколко релации с една таблица с дати, може да се наложи да създадете специални мерки, които да пренасят неактивни релации с помощта на функцията USERELATIONSHIP. От друга страна създаването на множество таблици с дати може да е объркващо в списъка на полетата и тъй като имате повече таблици в модела на данни, това ще изисква повече памет. Експериментирайте с това, което е най-добро за вас.
Свойство Date Table
Свойството Date Table задава метаданни, необходими за Time-Intelligence функции като TOTALYTD, PREVIOUSMONTH и DATESBETWEEN, за да работят правилно. Когато се извършва изчисление с помощта на една от тези функции, ядрото за формули на Power Pivot знае къде да отиде, за да получи датите, от които се нуждае.
Предупреждение: Ако това свойство не е зададено, мерките, които използват функциите DAX Time-Intelligence, може да не връщат правилни резултати.
Когато зададете свойството "Таблица с дати", задавате таблица с дати и колона с дати от типа данни "Дата и час".
Как да: Задаване на свойството "Таблица с дати"
-
В прозореца на PowerPivot изберете таблицата Календар .
-
В раздела Проектиране щракнете върху Маркирай като таблица с дати.
-
В диалоговия прозорец Маркирай като таблица с дати изберете колона с уникални стойности и типа данни "Дата".
Работа с времето
Всички стойности на дати с тип данни "Дата" в Excel или SQL Server всъщност са число. Включени в това число, са цифрите, които препращат към даден час. В много случаи това време за всеки ред е полунощ. Например ако поле DateTimeKey в таблица с факти Продажби има стойности като 19.10.2010 12:00:00 AM, това означава, че стойностите са с нивото на точност за деня. Ако стойностите на полето DateTimeKey имат включен час, например 19.10.2010 8:44:00 AM, това означава, че стойностите са с ниво на точност минута. Стойностите може също да са с точност от час или дори с ниво на точност секунди. Нивото на точност в стойността на часа ще има значително влияние върху начина, по който създавате вашата таблица с дати и релациите между нея и таблицата с факти.
Трябва да определите дали ще агрегирате вашите данни с ниво на точност за деня, или с ниво на точност във времето. С други думи, може да искате да използвате колони в таблицата с дати, като например Сутрин, Следобед или Час, като полета за дата на час в областите Ред, Колона или Филтриране на обобщена таблица.
Забележка: Дните са най-малката единица време, с която могат да работят функциите за времева аналитична информация на DAX. Ако не е необходимо да работите със стойности за време, трябва да намалите точността на вашите данни, за да използвате дните като минимална единица.
Ако възнамерявате да агрегирате данните си до ниво час, таблицата с дати ще се нуждае от колона с дати с включен час. Всъщност тя ще се нуждае от колона с дати с един ред за всеки час или може би дори всяка минута от всеки ден за всяка година в диапазона от дати. Това е така, защото за да създадете релация между колоната DateTimeKey в таблицата с факти и колоната за дата в таблицата с дати, трябва да имате съвпадащи стойности. Както можете да си представите, ако включите много години, това може да направи много голяма маса за среща.
В повечето случаи обаче искате да агрегирате данните си само за деня. С други думи, ще използвате колони като Година, Месец, Седмица или Ден от седмицата като полета в областите Ред, Колона или Филтър на обобщена таблица. В този случай колоната за дата в таблицата с дати трябва да съдържа само един ред за всеки ден в годината, както описахме по-рано.
Ако колоната с дата включва ниво на точност във времето, но ще агрегирате само на ниво ден, за да създадете релацията между таблицата с факти и таблицата с дати, може да се наложи да промените таблицата с факти, като създадете нова колона, която отрязва стойностите в колоната за дата до стойност за ден. С други думи, преобразувайте стойност от 19.10.2010 г. 8:44:00ч . в 19.10.2010 г., 12:00:00 ч. След това можете да създадете релацията между тази нова колона и колоната за дата в таблицата с дати, защото стойностите съвпадат.
Да разгледаме пример. Това изображение показва колона DateTimeKey в таблицата с факти Продажби. Всички агрегирания за данните в тази таблица трябва да бъдат само на ниво ден, като се използват колони в таблицата с дати на календара, като година, месец, тримесечие и др. Часът, включен в стойността, не е от значение, а само действителната дата.
Тъй като не е нужно да анализираме тези данни на ниво час, не ни трябва колоната "Дата" в таблицата "Календар", за да включваме по един ред за всеки час и всяка минута от всеки ден във всяка година. Така че колоната "Дата" в нашата таблица с дати изглежда така:
За да създадете релация между колоната DateTimeKey в таблицата "Продажби" и колоната "Дата" в таблицата "Календар", можем да създадем нова изчисляема колона в таблицата с факти "Продажби" и да използваме функцията TRUNC , за да отрязваме стойността за дата и час в колоната DateTimeKey в стойност за дата, която съответства на стойностите в колоната "Дата" в таблицата "Календар". Нашата формула изглежда така:
=TRUNC([DateTimeKey];0)
Това ни дава нова колона (наименувахме DateKey) с датата от колоната DateTimeKey и час 12:00:00 за всеки ред:
Сега можем да създадем релация между тази нова колона (DateKey) и колоната "Дата" в таблицата "Календар".
По същия начин можем да създадем изчисляема колона в таблицата Sales, която намалява точността на времето в колоната DateTimeKey до нивото на точност на часа. В този случай функцията TRUNC няма да работи, но все пак можем да използваме други функции за дата и час на DAX, за да извлечем и съединим нова стойност до ниво на точност от един час. Можем да използваме формула като тази:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Новата ни колона изглежда така:
При условие че нашата колона "Дата" в таблицата с дати има стойности с ниво на точност за часа, можем след това да създадем релация между тях.
Правене на датите по-използваеми
Много от колоните с дати, които създавате в таблицата с дати, са необходими за други полета, но всъщност не са толкова полезни за анализа. Например полето DateKey в таблицата "Продажби", към която се позовахме и показваме в тази статия, е важно, защото за всяка транзакция тази транзакция се записва като възникнала на определена дата и час. Но от гледна точка на анализа и отчитането не е всичко, което е полезно, тъй като не можем да го използваме като поле за ред, колона или филтър в обобщена таблица или отчет.
По същия начин в нашия пример колоната "Дата" в таблицата "Календар" е много полезна, критично всъщност, но не можете да я използвате като измерение в обобщена таблица.
За да запазите таблиците и колоните в тях възможно най-полезни и за да направите списъците с полета на отчет на Обобщена таблица или Power View по-лесни за навигиране, е важно да скриете ненужните колони от клиентските инструменти. Може също да искате да скриете определени таблици. Таблицата "Празници", показана по-рано, съдържа празнични дати, които са важни за определени колони в таблицата "Календар", но не можете да използвате колоните "Дата" и "Празник" в самата таблица "Празници" като полета в обобщена таблица. Тук отново, за да направите полето Списъци по-лесно за навигиране, можете да скриете цялата таблица "Празници".
Друг важен аспект на работата с дати е конвенциите за именуване. Можете да наименувате таблици и колони в Power Pivot каквото искате. Но имайте предвид, особено ако ще споделяте работната книга с други потребители, добра конвенция за именуване улеснява идентифицирането на таблици и дати, не само в Списъци на полета, но и в Power Pivot и в DAX формули.
След като имате таблица с дати във вашия модел на данни, можете да започнете да създавате мерки, които ще ви помогнат да извлечете максимума от данните си. Някои може да са просто сумиране на общите суми на продажбите за текущата година, а други може да са по-сложни, където трябва да филтрирате по определен диапазон от уникални дати. Научете повече в Мерки в Power Pivot и Функции за интелигентно време.
Приложение
Преобразуване на дати от текстов тип данни в тип данни за дата
В някои случаи таблица с данни за транзакции може да съдържа дати от текстов тип данни. Т.е. дата, която се показва като 2012-12-04T11:47:09, всъщност изобщо не е дата или поне не типа на датата, която Power Pivot може да разбере. Това всъщност е просто текст, който чете като дата. За да се създаде релация между колона за дата в таблицата с факти и колона с дати в таблица с дати, и двете колони трябва да бъдат от тип данни Date .
Обикновено, когато се опитате да промените типа на данните за колона с дати, които са текстов тип данни, на данни от тип "дата", Power Pivot може да интерпретира датите и автоматично да я преобразува в тип данни "истинска дата". Ако Power Pivot не може да извърши преобразуване на тип данни, ще получите грешка за несъответствие на типове.
Можете обаче да конвертирате датите в тип данни true date. Можете да създадете нова изчисляема колона и да използвате DAX формула, за да анализирате годината, месеца, деня, часа и т.н. от текстовите низове и след това да я комбинирате отново по начин, който Power Pivot може да чете като истинска дата.
В този пример импортирахме таблица с факти с име "Продажби" в Power Pivot. Тя съдържа колона с име DateTime. Стойностите изглеждат по следния начин:
Ако разгледаме Тип данни във Форматиране, групата Начало на Power Pivot, виждаме, че това е текстов тип данни.
Не можем да създадем релация между колоната DateTime и колоната Date в нашата таблица с дати, защото типовете данни не съвпадат. Ако се опитаме да променим типа на данните на "Дата", получаваме грешка поради несъответствие на тип:
В този случай Power Pivot не можа да преобразува типа данни от текст в дата. Все още можем да използваме тази колона, но за да я превърнем в тип данни "истинска дата", трябва да създадем нова колона, която анализира текста и го създава отново в стойност, която Power Pivot може да направи тип данни "Дата".
Не забравяйте, че от раздела Работа с час по-горе в тази статия; освен ако не е необходимо вашият анализ да бъде с ниво на точност за определен ден, трябва да преобразувате датите в таблицата с факти в дневно ниво на точност. Имайки това наум, искаме стойностите в новата ни колона да бъдат с нивото на точност за деня (без времето). Можем както да преобразуваме стойностите в колоната DateTime в данни от тип "дата", така и да премахнем нивото на точност за време със следната формула:
=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))
Сега, когато имаме колона "Дата" от типа данни "Дата", можем да създадем релация между нея и колона с дати в дата.
Допълнителни ресурси
QuickStart: Научете основите на DAX за 30 минути
