Tabele dat w dodatku Power Pivot są niezbędne do przeglądania i obliczania danych w czasie. Ten artykuł zawiera szczegółowe informacje na temat tabel dat i sposobu ich tworzenia w dodatku Power Pivot. W szczególności w tym artykule opisano:
-
Dlaczego tabela dat jest ważna do przeglądania i obliczania danych według dat i godzin.
-
Jak dodać tabelę dat do modelu danych za pomocą dodatku Power Pivot.
-
Jak utworzyć nowe kolumny dat, takie jak Rok, Miesiąc i Okres w tabeli dat.
-
Jak tworzyć relacje między tabelami dat a tabelami faktów.
-
Jak pracować z czasem.
Ten artykuł jest przeznaczony dla użytkowników nowych użytkowników dodatku Power Pivot. Jednak ważne jest, aby dobrze zrozumieć importowanie danych, tworzenie relacji oraz tworzenie kolumn i miar obliczeniowych.
W tym artykule nie opisano sposobu używania funkcji języka DAX Time-Intelligence w formułach pomiarowych. Aby uzyskać więcej informacji na temat tworzenia miar za pomocą funkcji analizy czasowej języka DAX, zobacz Analiza czasowa w dodatku Power Pivot w programie Excel.
Uwaga: W dodatku Power Pivot nazwy "miara" i "pole obliczeniowe" są synonimem. W tym artykule używamy miary nazwy. Aby uzyskać więcej informacji, zobacz Miary w dodatku Power Pivot.
Spis treści
Opis tabel dat
Niemal wszystkie analizy danych obejmują przeglądanie i porównywanie danych w dniach i godzinach. Możesz na przykład zsumować kwoty sprzedaży za miniony kwartał obrachunkowy, a następnie porównać te sumy z innymi kwartałami lub obliczyć saldo zamknięcia na koniec miesiąca dla konta. W każdym z tych przypadków daty są używane jako sposób grupowania i agregowania transakcji sprzedaży lub sald w danym okresie.
Raport programu Power View
Tabela dat może zawierać wiele różnych reprezentacji dat i godzin. Na przykład tabela dat często zawiera kolumny, takie jak Rok obrachunkowy, Miesiąc, Kwartał lub Okres, które można wybrać jako pola z listy pól podczas krojenia i filtrowania danych w tabelach przestawnych lub raportach programu Power View.
Lista pól programu Power View
Aby kolumny dat, takie jak Rok, Miesiąc i Kwartał, zawierały wszystkie daty w odpowiednim zakresie, tabela dat musi zawierać co najmniej jedną kolumnę z ciągłym zestawem dat. Oznacza to, że ta kolumna musi zawierać jeden wiersz dla każdego dnia dla każdego roku uwzględniony w tabeli dat.
Jeśli na przykład dane, które chcesz przeglądać, zawierają daty od 1 lutego 2010 r. do 30 listopada 2012 r., a raportujesz rok kalendarzowy, będziesz chcieć utworzyć tabelę dat z co najmniej zakresem dat od 1 stycznia 2010 r. do 31 grudnia 2012 r. Każdy rok w tabeli dat musi zawierać wszystkie dni każdego roku. Jeśli będziesz regularnie odświeżać dane przy użyciu nowszych danych, możesz chcieć uruchomić datę końcową o rok lub dwa, więc nie musisz aktualizować tabeli dat w miarę upływu czasu.
Tabela dat z ciągłym zestawem dat
Jeśli raportujesz rok obrachunkowy, możesz utworzyć tabelę dat z ciągłym zestawem dat dla każdego roku obrachunkowego. Jeśli na przykład rok obrachunkowy rozpoczyna się 1 marca i masz dane z lat obrachunkowych 2010 w górę do bieżącej daty (na przykład w roku obrotowym 2013), możesz utworzyć tabelę dat, która rozpoczyna się 2009-03-01 i obejmuje co najmniej każdy dzień w każdym roku obrachunkowym do ostatniej daty w roku obrachunkowym 2013.
Jeśli będziesz raportować zarówno rok kalendarzowy, jak i rok obrachunkowy, nie musisz tworzyć oddzielnych tabel dat. Pojedyncza tabela dat może zawierać kolumny dla roku kalendarzowego, roku obrachunkowego, a nawet trzynastu czterotygodniowych kalendarzy okresów. Ważne jest to, że tabela dat zawiera ciągły zestaw dat dla wszystkich uwzględnionych lat.
Dodawanie tabeli daty do modelu danych
Istnieje kilka sposobów dodawania tabeli daty do modelu danych:
-
Importowanie z relacyjnej bazy danych lub innego źródła danych.
-
Utwórz tabelę dat w programie Excel, a następnie skopiuj lub połącz z nową tabelą w dodatku Power Pivot.
-
Importuj z Microsoft Azure Marketplace.
Przyjrzyjmy się dokładniej każdemu z nich.
Importowanie z relacyjnej bazy danych
Jeśli zaimportujesz część lub wszystkie dane z magazynu danych lub innej relacyjnej bazy danych, prawdopodobnie istnieje już tabela dat i relacje między nią a pozostałą częścią importowanych danych. Daty i format prawdopodobnie będą zgodne z datami zawartymi w danych faktów, a daty prawdopodobnie zaczną się dobrze w przeszłości i zaszły daleko w przyszłość. Tabela dat, którą chcesz zaimportować, może być bardzo duża i zawierać zakres dat wykraczających poza to, co należy uwzględnić w modelu danych. Zaawansowane funkcje filtru Kreatora importu tabel w dodatku Power Pivot umożliwiają selektywne wybieranie tylko dat i konkretnych kolumn, których naprawdę potrzebujesz. Może to znacznie zmniejszyć rozmiar skoroszytu i zwiększyć wydajność.
Kreator importu tabeli
W większości przypadków nie trzeba tworzyć żadnych dodatkowych kolumn, takich jak Rok obrachunkowy, Tydzień, Nazwa miesiąca itp., ponieważ będą już istnieć w zaimportowanej tabeli. Jednak w niektórych przypadkach po zaimportowaniu tabeli daty do modelu danych może być konieczne utworzenie dodatkowych kolumn dat w zależności od konkretnej potrzeby raportowania. Na szczęście można to łatwo zrobić za pomocą języka DAX. Później dowiesz się więcej o tworzeniu pól tabeli dat. Każde środowisko jest inne. Jeśli nie masz pewności, czy źródła danych mają powiązaną datę lub tabelę kalendarza, skontaktuj się z administratorem bazy danych.
Tworzenie tabeli daty w programie Excel
Możesz utworzyć tabelę dat w programie Excel, a następnie skopiować ją do nowej tabeli w modelu danych. Jest to naprawdę dość łatwe do zrobienia i daje dużo elastyczności.
Tworząc tabelę dat w programie Excel, rozpoczynasz od jednej kolumny z ciągłym zakresem dat. Następnie można utworzyć dodatkowe kolumny, takie jak Rok, Kwartał, Miesiąc, Rok obrachunkowy, Okres itp., w arkuszu programu Excel przy użyciu formuł programu Excel lub po skopiowaniu tabeli do modelu danych można utworzyć je jako kolumny obliczeniowe. Tworzenie dodatkowych kolumn dat w dodatku Power Pivot opisano w sekcji Dodawanie nowych kolumn daty do tabeli dat w dalszej części tego artykułu.
Jak: Tworzenie tabeli daty w programie Excel i kopiowanie jej do modelu danych
-
W programie Excel w pustym arkuszu w komórce A1 wpisz nazwę nagłówka kolumny, aby zidentyfikować zakres dat. Zazwyczaj jestto data, data/godzina lub klucz_daty.
-
W komórce A2 wpisz datę początkową. Na przykład 2010-01-01.
-
Kliknij uchwyt wypełniania i przeciągnij go w dół do numeru wiersza zawierającego datę końcową. Na przykład 2016-12-31.
-
Zaznacz wszystkie wiersze w kolumnie Data (łącznie z nazwą nagłówka w komórce A1).
-
W grupie Style kliknij pozycję Formatuj jako tabelę, a następnie wybierz styl.
-
W oknie dialogowym Formatowanie jako tabeli kliknij przycisk OK.
-
Skopiuj wszystkie wiersze, łącznie z nagłówkiem.
-
W dodatku Power Pivot na karcie Narzędzia główne kliknij pozycję Wklej.
-
W polu Wklej podgląd > nazwa tabeli wpisz nazwę, taką jak Data lub Kalendarz. Pozostaw zaznaczone pole wyboru Użyj pierwszego wiersza jako nagłówkówkolumn, a następnie kliknij przycisk OK.
Nowa tabela dat (w tym przykładzie o nazwie Kalendarz) w dodatku Power Pivot wygląda następująco:
Uwaga: Tabelę połączoną można również utworzyć za pomocą polecenia Dodaj do modelu danych. Spowoduje to jednak niepotrzebne duże rozmiary skoroszytu, ponieważ skoroszyt ma dwie wersje tabeli dat. jeden w programie Excel i jeden w dodatku Power Pivot.
Uwaga: Data nazwy to słowo kluczowe w dodatku Power Pivot. Jeśli nazwisz tabelę utworzoną w dodatku Power Pivot Date, musisz ująć nazwę tabeli w cudzysłowy pojedyncze we wszystkich formułach języka DAX, które odwołują się do niej w argumentie. Wszystkie przykładowe obrazy i formuły w tym artykule dotyczą tabeli dat utworzonej w dodatku Power Pivot o nazwie Kalendarz.
W modelu danych jest teraz dostępna tabela dat. Za pomocą języka DAX możesz dodać nowe kolumny dat, takie jak Rok, Miesiąc itp.
Dodawanie nowych kolumn daty do tabeli dat
Tabela dat z jedną kolumną daty, która zawiera jeden wiersz dla każdego dnia dla każdego roku, jest ważna do definiowania wszystkich dat w zakresie dat. Jest również konieczne utworzenie relacji między tabelą faktów a tabelą dat. Jednak ta pojedyncza kolumna daty z jednym wierszem na każdy dzień nie jest przydatna podczas analizowania według dat w tabeli przestawnej lub raporcie programu Power View. Tabela dat ma zawierać kolumny ułatwiające agregowanie danych dla zakresu lub grupy dat. Możesz na przykład zsumować kwoty sprzedaży według miesiąca lub kwartału albo utworzyć miarę obliczaną wzrost rok do roku. W każdym z tych przypadków tabela dat wymaga kolumn roku, miesiąca lub kwartału, które umożliwiają agregowanie danych dla tego okresu.
Jeśli tabela dat została zaimportowana z relacyjnego źródła danych, może już zawierać różne typy kolumn dat. W niektórych przypadkach możesz zechcieć zmodyfikować niektóre z tych kolumn lub utworzyć dodatkowe kolumny dat. Jest to szczególnie ważne, jeśli tworzysz własną tabelę dat w programie Excel i kopiujesz ją do modelu danych. Na szczęście tworzenie nowych kolumn dat w dodatku Power Pivot jest dość łatwe dzięki funkcjom daty i godziny w języku DAX.
Porada: Jeśli jeszcze nie współpracowano z językiem DAX, warto rozpocząć naukę za pomocą przewodnika Szybki start: poznaj podstawy języka DAX w ciągu 30 minut w Office.com.
Funkcje daty i godziny w języku DAX
Jeśli kiedykolwiek pracowano z funkcjami daty i godziny w formułach programu Excel, prawdopodobnie znasz funkcje Daty i Godziny. Chociaż te funkcje są podobne do ich odpowiedników w programie Excel, istnieją pewne istotne różnice:
-
W funkcjach Data i Godzina języka DAX jest używany typ danych datetime.
-
Mogą one przyjmować wartości z kolumny jako argument.
-
Mogą być używane do zwracania i/lub manipulowania wartościami dat.
Te funkcje są często używane podczas tworzenia niestandardowych kolumn dat w tabeli dat, więc należy je zrozumieć. Użyjemy kilku z tych funkcji, aby utworzyć kolumny dla kolumn Rok, Kwartał, Rok Obrachunkowy itd.
Uwaga: Funkcje daty i godziny w języku DAX nie są takie same jak funkcje analizy czasowej. Dowiedz się więcej o analizie czasowej w dodatku Power Pivot w programie Excel.
Język DAX zawiera następujące funkcje daty i godziny:
Istnieje też wiele innych funkcji języka DAX, których można używać w formułach. Na przykład w wielu opisanych tutaj formułach są używane funkcje matematyczne i trygonometryczne , takie jak MOD i TRUNC, funkcje logiczne , takie jak JEŻELI, oraz funkcje tekstowe , takie jak FORMAT Aby uzyskać więcej informacji o innych funkcjach języka DAX, zobacz sekcję Dodatkowe zasoby w dalszej części tego artykułu.
Przykłady formuł dla roku kalendarzowego
W poniższych przykładach opisano formuły służące do tworzenia dodatkowych kolumn w tabeli dat o nazwie Kalendarz. Jedna kolumna o nazwie Data już istnieje i zawiera ciągły zakres dat od 2010-01-01 do 2016-12-31.
Rok
=ROK([data])
W tej formule funkcja ROK zwraca rok z wartości w kolumnie Data. Ponieważ wartość w kolumnie Data ma typ danych data/godzina, funkcja ROK wie, jak zwrócić rok.
Miesiąc
=MIESIĄC([data])
W tej formule, podobnie jak w przypadku funkcji ROK, można po prostu użyć funkcji MIESIĄC , aby zwrócić wartość miesiąca z kolumny Data.
Kwartał
=INT(([Miesiąc]+2)/3)
W tej formule funkcja ZAOKR.DO.TEKST zwraca wartość daty jako liczbę całkowitą. Argument określony dla funkcji INT to wartość z kolumny Miesiąc, dodaj wartość 2, a następnie podziel ją przez 3, aby uzyskać nasz kwartał, 1 do 4.
Nazwa miesiąca
=FORMAT([data]"mmmm")
Aby uzyskać nazwę miesiąca w tej formule , użyjemy funkcji FORMAT, aby przekonwertować wartość liczbową z kolumny Data na tekst. Jako pierwszy argument określamy kolumnę Date, a następnie format. chcemy, aby w nazwie miesiąca były wyświetlane wszystkie znaki, dlatego używamy ciągu "mmmm". Nasz wynik wygląda następująco:
Jeśli chcesz zwrócić nazwę miesiąca skróconą do trzech liter, w argumacie formatu użyjemy ciągu "mmm".
Dzień tygodnia
=FORMAT([data]"ddd")
W tej formule do uzyskania nazwy dnia jest używana funkcja FORMAT. Ponieważ chcemy mieć tylko skróconą nazwę dnia, w argumacie formatu określamy wartość "ddd".
Przykładowa tabela przestawna
Po utworzeniu pól dat, takich jak Rok, Kwartał, Miesiąc itp., można ich używać w tabeli przestawnej lub raporcie. Na przykład na poniższej ilustracji przedstawiono pole KwotaSprzedaży z tabeli Fakt sprzedaży w polach WARTOŚCI oraz Rok i Kwartał z tabeli wymiarów Kalendarz w wierszach. Wartość KwotaSprzedaży jest agregowane w kontekście roku i kwartału.
Przykłady formuł dla roku obrachunkowego
Rok obrachunkowy
=JEŻELI([Miesiąc]<= 6;[Rok];[Rok]+1)
W tym przykładzie rok obrachunkowy rozpoczyna się 1 lipca.
Nie ma funkcji, która umożliwia wyodrębnianie roku obrachunkowego z wartości daty, ponieważ daty rozpoczęcia i zakończenia roku obrachunkowego często różnią się od dat roku kalendarzowego. Aby uzyskać rok obrachunkowy, najpierw użyjemy funkcji JEŻELI w celu sprawdzenia, czy wartość argumentu Miesiąc jest mniejsza niż lub równa 6. W drugim argumacie, jeśli wartość argumentu Miesiąc jest mniejsza niż lub równa 6, zwróć wartość z kolumny Rok. Jeśli nie, zwróć wartość z pozycji Rok i dodaj 1.
Innym sposobem określenia wartości miesiąca końcowego roku obrachunkowego jest utworzenie miary określającej po prostu miesiąc. Na przykład: RR:=6. Następnie można odwołać się do nazwy miary w miejsce numeru miesiąca. Na przykład =JEŻELI([Miesiąc]<=[ROK.MIES];[Rok];[Rok]+1). Zapewnia to większą elastyczność podczas odwoływania się do miesiąca końcowego roku obrachunkowego w kilku różnych formułach.
Miesiąc obrachunkowy
=JEŻELI([Miesiąc]<= 6, 6+[Miesiąc]; [Miesiąc]- 6)
W tej formule określamy, czy wartość argumentu [Miesiąc] jest mniejsza niż lub równa 6, a następnie bierze wartość 6 i dodaje wartość z miesiąca, w przeciwnym razie odejmuje wartość 6 od wartości od [Miesiąc].
Kwartał obrachunkowy
=INT(([FiscalMonth]+2)/3)
Formuła używana dla argumentu FiscalQuarter jest dużo taka sama jak w przypadku kwartału w roku kalendarzowym. Jedyną różnicą jest to, że określamy wartość [FiscalMonth] zamiast [Month].
Święta lub daty specjalne
Może być konieczne uwzględnienie kolumny daty, która wskazuje, że niektóre daty to święta lub inna data specjalna. Możesz na przykład zsumować sumy sprzedaży za nowy rok, dodając pole Święta do tabeli przestawnej, jako fragmentator lub filtr. W innych przypadkach można wykluczyć te daty z innych kolumn dat lub miary.
W tym święta lub specjalne dni jest dość proste. W programie Excel możesz utworzyć tabelę zawierającą daty, które chcesz uwzględnić. Następnie możesz skopiować model danych lub użyć go, aby dodać go do modelu danych jako tabelę połączoną. W większości przypadków nie jest konieczne tworzenie relacji między tabelą a tabelą Kalendarz. Wszystkie formuły odwołujące się do niej mogą zwracać wartości za pomocą funkcji WYSZUKAJ.WARTOŚĆ .
Poniżej przedstawiono przykładową tabelę utworzoną w programie Excel zawierającą święta, które mają zostać dodane do tabeli dat:
|
Data |
Święto |
|---|---|
|
1/1/2010 |
Nowe lata |
|
11/25/2010 |
Dziękczynienie |
|
12/25/2010 |
Boże Narodzenie |
|
2011-01-01 |
Nowe lata |
|
11/24/2011 |
Dziękczynienie |
|
12/25/2011 |
Boże Narodzenie |
|
2012-01-01 |
Nowe lata |
|
2012-11-22 |
Dziękczynienie |
|
12/25/2012 |
Boże Narodzenie |
|
1/1/2013 |
Nowe lata |
|
11/28/2013 |
Dziękczynienie |
|
12/25/2013 |
Boże Narodzenie |
|
11/27/2014 |
Dziękczynienie |
|
12/25/2014 |
Boże Narodzenie |
|
2014-01-01 |
Nowe lata |
|
11/27/2014 |
Dziękczynienie |
|
12/25/2014 |
Boże Narodzenie |
|
1/1/2015 |
Nowe lata |
|
11/26/2014 |
Dziękczynienie |
|
12/25/2015 |
Boże Narodzenie |
|
2016-01-01 |
Nowe lata |
|
11/24/2016 |
Dziękczynienie |
|
12/25/2016 |
Boże Narodzenie |
W tabeli dat utworzymy kolumnę o nazwie Święta i użyjemy następującej formuły:
=SZUKAJ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
Przyjrzyjmy się tej formule dokładniej.
Funkcja WYSZUKAJ.WARTOŚĆ służy do uzyskiwania wartości z kolumny Święta w tabeli Święta. W pierwszym argumentze określamy kolumnę, w której będzie znajdować się nasza wartość wyniku. Określamy kolumnę Święta w tabeli Święta , ponieważ jest to wartość, którą chcemy zwrócić.
=SZUKAJ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
Następnie określamy drugi argument — kolumnę wyszukiwania zawierającą daty, które mają zostać wyszukane. Kolumnę Data określamy w tabeli Święta , w następującym przykładzie:
=SZUKAJ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
Na koniec określamy kolumnę w tabeli Kalendarz zawierającą daty, które mają zostać wyszukane w tabeli Święta . Jest to oczywiście kolumna Data w tabeli Kalendarz .
=SZUKAJ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
Kolumna Święta zwróci nazwę świąt dla każdego wiersza z wartością daty odpowiadającą dacie w tabeli Święta.
Kalendarz niestandardowy — trzynaście okresów czterotygodniowych
Niektóre organizacje, takie jak handel detaliczny lub usługi gastronomiczne, często zgłaszają różne okresy, na przykład trzynaście okresów czterotygodniowych. W przypadku trzynastu tygodniowego kalendarza okresu każdy okres wynosi 28 dni; Dlatego każdy okres zawiera cztery poniedziałki, cztery wtorki, cztery środy i tak dalej. Każdy okres zawiera taką samą liczbę dni i zwykle święta przypadają w tym samym okresie każdego roku. Możesz rozpocząć okres w dowolnym dniu tygodnia. Podobnie jak w przypadku dat w kalendarzu lub roku obrachunkowym, za pomocą języka DAX można tworzyć dodatkowe kolumny z datami niestandardowymi.
W poniższych przykładach pierwszy pełny okres rozpoczyna się w pierwszą niedzielę roku obrachunkowego. W tym przypadku rok obrachunkowy rozpoczyna się 7/1.
Tydzień
Ta wartość daje nam numer tygodnia rozpoczynający się od pierwszego pełnego tygodnia w roku obrachunkowym. W tym przykładzie pierwszy pełny tydzień rozpoczyna się w niedzielę, więc pierwszy pełny tydzień w pierwszym roku obrachunkowym w tabeli Kalendarz rozpoczyna się 2010-07-04 i trwa do ostatniego pełnego tygodnia w tabeli Kalendarz. Chociaż sama ta wartość nie jest przydatna w analizie, konieczne jest obliczenie do użycia w innych formułach okresów 28-dniowych.
=INT([data]-40356)/7)
Przyjrzyjmy się tej formule dokładniej.
Najpierw tworzymy formułę zwracającą wartości z kolumny Date jako liczbę całkowitą, w następującym przykładzie:
=INT([data])
Następnie chcemy szukać pierwszej niedzieli w pierwszym roku obrachunkowym. Widzimy, że jest 2010-07-04.
Teraz odejmij 40356 (czyli liczbę całkowitą dla daty 2010-06-27, ostatnią niedzielę od poprzedniego roku obrachunkowego) od tej wartości, aby uzyskać liczbę dni od początku dnia w naszej tabeli Kalendarz, w następujący następujący:
=INT([data]-40356)
Następnie podziel wynik przez 7 (dni w tygodniu), tak jak w następującym przykładzie:
=INT(([data]-40356)/7)
Wynik wygląda następująco:
Period
Okres w tym kalendarzu niestandardowym zawiera 28 dni i zawsze zaczyna się w niedzielę. Ta kolumna zwróci liczbę okresu rozpoczynającego się od pierwszej niedzieli w pierwszym roku obrachunkowym.
=INT(([Tydzień]+3)/4)
Przyjrzyjmy się tej formule dokładniej.
Najpierw tworzymy formułę zwracającą wartość z kolumny Tydzień jako liczbę całkowitą, na przykład następującą:
=INT([Tydzień])
Następnie dodaj 3 do tej wartości, w następującym celu:
=INT([Tydzień]+3)
Następnie podziel wynik przez 4, tak jak pokazano w poniższym przykładzie:
=INT(([Tydzień]+3)/4)
Wynik wygląda następująco:
Okres roku obrachunkowego
Ta wartość zwraca rok obrachunkowy dla danego okresu.
=INT(([Okres]+12)/13)+2008
Przyjrzyjmy się tej formule dokładniej.
Najpierw tworzymy formułę, która zwraca wartość z kropki i dodaje 12:
= ([Okres]+12)
Wynik dzielimy przez 13, ponieważ w roku obrachunkowym istnieje trzynaście 28-dniowych okresów:
=(([Okres]+12)/13)
Dodajemy rok 2010, ponieważ jest to pierwszy rok w tabeli:
=(([Okres]+12)/13)+2010
Na koniec używamy funkcji ROZKŁ.INT, aby usunąć dowolny ułamek wyniku i zwrócić liczbę całkowitą podzieloną przez 13, w następujący sposób:
=INT(([Okres]+12)/13)+2010
Wynik wygląda następująco:
Okres roku obrachunkowego
Ta wartość zwraca liczbę okresów od 1 do 13, rozpoczynając od pierwszego pełnego okresu (rozpoczynającego się od niedzieli) w każdym roku obrachunkowym.
=JEŻELI(MOD([Okres];13); MOD([Okres];13);13)
Ta formuła jest nieco bardziej złożona, więc opiszemy ją najpierw w języku, który lepiej rozumiemy. Ta formuła stanowi, że wartość z [Kropka] jest dzielenia przez 13, aby uzyskać liczbę okresów (1–13) w roku. Jeśli ta liczba to 0, zwróć wartość 13.
Najpierw tworzymy formułę zwracającą resztę wartości z okresu o 13. Możemy użyć funkcji MOD (matematycznych i trygonometrycznych) w następujący sposób:
=MOD([Okres];13)
W większości przypadków daje to oczekiwany wynik, z wyjątkiem sytuacji, gdy wartość argumentu Okres wynosi 0, ponieważ te daty nie przypadają w pierwszym roku obrachunkowym, jak w pierwszych pięciu dniach przykładowej tabeli daty kalendarza. Możemy się tym zająć za pomocą funkcji JEŻELI. W przypadku, gdy wynik jest 0, zwracamy 13, tak jak to:
=JEŻELI(MOD([Okres];13);MOD([Okres];13);13)
Wynik wygląda następująco:
Przykładowa tabela przestawna
Na poniższej ilustracji przedstawiono tabelę przestawną z polem KwotaSprzedaży z tabeli Fakt sprzedaży w polach WARTOŚCI oraz Pola Okres_okresu_roku i OkresInFiscalYear z tabeli wymiarów daty kalendarza w wierszach. KwotaSprzedaży jest agregowane w danym kontekście według roku obrachunkowego i 28-dniowego okresu w roku obrachunkowym.
Relacje
Po utworzeniu tabeli dat w modelu danych, aby rozpocząć przeglądanie danych w tabelach przestawnych i raportach oraz agregować dane na podstawie kolumn w tabeli wymiarów daty, należy utworzyć relację między tabelą faktów a danymi transakcji a tabelą dat.
Ponieważ należy utworzyć relację opartą na datach, należy upewnić się, że została utworzona relacja między kolumnami, których wartości mają typ danych datetime (Date).
Dla każdej wartości daty w tabeli faktów powiązana kolumna odnośnika w tabeli dat musi zawierać pasujące wartości. Na przykład wiersz (rekord transakcji) w tabeli Fakt sprzedaży o wartości 2012-08-15 12:00 w kolumnie DateKey musi mieć odpowiadającą jej wartość w powiązanej kolumnie Data w tabeli daty (o nazwie Kalendarz). Jest to jeden z najważniejszych powodów, dla których kolumna daty w tabeli dat ma zawierać ciągły zakres dat zawierający ewentualną datę w tabeli faktów.
Uwaga: Kolumna daty w każdej tabeli musi mieć ten sam typ danych (Data), jednak format każdej kolumny nie ma znaczenia.
Uwaga: Jeśli dodatek Power Pivot nie umożliwia tworzenia relacji między obiema tabelami, pola dat mogą nie przechowywać daty i godziny na tym samym poziomie dokładności. W zależności od formatowania kolumn wartości mogą wyglądać tak samo, ale mogą być przechowywane w inny sposób. Dowiedz się więcej o pracy z czasem.
Uwaga: Unikaj używania kluczy zastępczych typu integer w relacjach. Podczas importowania danych z relacyjnego źródła danych często kolumny daty i godziny są reprezentowane przez klucz zastępczy, który jest kolumną całkowitą używaną do reprezentowania unikatowej daty. W dodatku Power Pivot należy unikać tworzenia relacji przy użyciu daty/godziny całkowitej, a zamiast tego używać kolumn zawierających unikatowe wartości z typem danych daty. Mimo że używanie kluczy zastępczych jest uznawane za najlepsze rozwiązanie w tradycyjnych magazynach danych, klucze całkowite nie są potrzebne w dodatku Power Pivot i mogą utrudniać grupowanie wartości w tabelach przestawnych według różnych okresów dat.
Jeśli podczas próby utworzenia relacji zostanie wyświetlony błąd niezgodności Typ, prawdopodobnie kolumna w tabeli faktów nie ma typu danych Data. Może się tak zdarzyć, gdy dodatek Power Pivot nie może automatycznie przekonwertować danych nieaktualnych (zwykle typu danych tekstowych) na typ danych daty. Możesz nadal używać kolumny w tabeli faktów, ale musisz przekonwertować dane za pomocą formuły języka DAX w nowej kolumnie obliczeniowej. Zobacz Konwertowanie dat typów danych tekstu na typ danych daty w dalszej sekcji dodatku.
Wiele relacji
W niektórych przypadkach może być konieczne utworzenie wielu relacji lub utworzenie wielu tabel dat. Jeśli na przykład w tabeli faktów Sprzedaż znajduje się wiele pól daty, takich jak DateKey, ShipDate i ReturnDate, wszystkie te pola mogą mieć relacje z polem Data w tabeli daty kalendarza, ale tylko jedna z nich może być aktywną relacją. W tym przypadku, ponieważ DateKey reprezentuje datę transakcji, a zatem najważniejszą datę, to najlepiej służyć jako aktywna relacja. Pozostałe mają nieaktywne relacje.
Poniższa tabela przestawna oblicza łączną sprzedaż według roku obrachunkowego i kwartału obrachunkowego. Miara o nazwie Łączna sprzedaż z formułą Łączna sprzedaż:=SUMA([KwotaSprzedaży])jest umieszczana w polach WARTOŚCI, a pola Rok Obrachunkowy i Kwartał Obrachunkowy z tabeli Data kalendarza są umieszczane w tabeli WIERSZE.
Ta prosta tabela przestawna działa poprawnie, ponieważ chcemy podsumować sprzedaż całkowitą według daty transakcji w kluczu DateKey. W miarę całkowitej sprzedaży są używane daty w kluczu daty i są sumowane według roku obrachunkowego i kwartału obrachunkowego, ponieważ istnieje relacja między kluczem date w tabeli Sprzedaż a kolumną Data w tabeli daty kalendarza.
Nieaktywne relacje
Ale co, jeśli chcemy podsumować naszą całkowitą sprzedaż nie według daty transakcji, ale według daty wysyłki? Potrzebujemy relacji między kolumną DataWysyłki w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz. Jeśli nie utworzymy tej relacji, nasze agregacje są zawsze oparte na dacie transakcji. Możemy jednak mieć wiele relacji, nawet jeśli tylko jedna może być aktywna, a ponieważ data transakcji jest najważniejsza, staje się aktywną relacją z tabelą Kalendarz.
W tym przypadku dataWysyłki ma nieaktywną relację, więc każda formuła miary utworzona w celu agregowania danych na podstawie dat wysyłki musi określać nieaktywną relację przy użyciu funkcji USERELATIONSHIP .
Na przykład ponieważ istnieje nieaktywna relacja między kolumną DataWysyłki w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz, możemy utworzyć miarę sumującą całkowitą sprzedaż według daty wysyłki. Aby określić relację, która ma być używana, jest używana następująca formuła:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ta formuła po prostu stwierdza: Oblicz sumę dla pola KwotaSprzedaży, ale przefiltruj ją przy użyciu relacji między kolumną DataWysyłki w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz.
Teraz, jeśli utworzymy tabelę przestawną i umieścimy miarę Łączna sprzedaż według daty wysyłki w wartościach, rok obrachunkowy i kwartał obrachunkowy w wierszach, zobaczymy tę samą sumę końcową, ale wszystkie pozostałe kwoty za rok obrachunkowy i kwartał obrachunkowy różnią się, ponieważ są oparte na dacie wysyłki, a nie na dacie transakcji.
Użycie relacji nieaktywnych umożliwia użycie tylko jednej tabeli daty, ale wymaga, aby wszelkie miary (takie jak Łączna sprzedaż według daty wysyłki) odwoływać się do nieaktywnej relacji w jej formule. Istnieje jeszcze jedna alternatywa, czyli używanie wielu tabel dat.
Wiele tabel dat
Innym sposobem pracy z wieloma kolumnami dat w tabeli faktów jest utworzenie wielu tabel dat i utworzenie między nimi oddzielnych aktywnych relacji. Przyjrzyjmy się ponownie naszemu przykładowi z tabeli Sprzedaż. Mamy trzy kolumny z datami, według których możemy chcieć zagregować dane:
-
Klucz_daty z datą sprzedaży dla każdej transakcji.
-
DataWysyłki — z datą i godziną, kiedy sprzedane towary zostały wysłane do klienta.
-
Data_zwrotu — z datą i godziną odebrania co najmniej jednego zwracanego elementu.
Pamiętaj, że najważniejsze jest pole DateKey z datą transakcji. Większość agregacji będziemy wykonywać na podstawie tych dat, dlatego z pewnością będziemy chcieli utworzyć relację między nią a kolumną Data w tabeli Kalendarz. Jeśli nie chcemy tworzyć nieaktywnych relacji między polami DataWysyłki i DataWysyłki oraz Data w tabeli Kalendarz, co wymaga stosowania specjalnych formuł miar, możemy utworzyć dodatkowe tabele dat dla daty wysyłki i daty zwrotu. Następnie możemy tworzyć aktywne relacje między nimi.
W tym przykładzie utworowaliśmy inną tabelę dat o nazwie ShipCalendar. Oznacza to oczywiście również tworzenie dodatkowych kolumn dat, a ponieważ te kolumny dat znajdują się w innej tabeli dat, chcemy je nazwać w taki sposób, aby odróżnić je od tych samych kolumn w tabeli Kalendarz. Na przykład utworowaliśmy kolumny o nazwach ShipYear, ShipMonth, ShipQuarter itd.
Jeśli utworzymy tabelę przestawną i umieścimy miarę Łączna sprzedaż w wartościach, a wartości ShipFiscalYear i ShipFiscalQuarter w wierszach będą takie same, jak w przypadku utworzenia nieaktywnej relacji i specjalnego pola obliczeniowego Łączna sprzedaż według daty wysyłki.
Każde z tych podejść wymaga starannego rozważenia. W przypadku używania wielu relacji z jedną tabelą dat może być konieczne utworzenie specjalnych miar, które przesyłają nieaktywne relacje przy użyciu funkcji USERELATIONSHIP. Z drugiej strony tworzenie wielu tabel dat może być mylące dla listy pól, a ponieważ w modelu danych jest więcej tabel, będzie to wymagało więcej pamięci. Poeksperymentuj z tym, co działa najlepiej dla Ciebie.
Właściwość Date Table
Właściwość Date Table ustawia metadane niezbędne do poprawnego działania funkcji Time-Intelligence, takich jak TOTALYTD, PREVIOUSMONTH i DATESBETWEEN. Po uruchomieniu obliczenia przy użyciu jednej z tych funkcji aparat formuł dodatku Power Pivot wie, gdzie można uzyskać potrzebne daty.
Ostrzeżenie: Jeśli ta właściwość nie jest ustawiona, miary używające funkcji języka DAX Time-Intelligence mogą nie zwracać prawidłowych wyników.
Podczas ustawiania właściwości Tabela dat należy określić tabelę dat i kolumnę daty typu danych Date (datetime).
Jak: Ustawianie właściwości Date Table
-
W oknie dodatku PowerPivot wybierz tabelę Kalendarz .
-
Na karcie Projektowanie kliknij pozycję Oznacz jako datę Tabela.
-
W oknie dialogowym Oznacz jako tabelę daty wybierz kolumnę z unikatowymi wartościami i typ danych Data.
Praca z czasem
Wszystkie wartości dat z typem danych Data w programie Excel lub SQL Server są w rzeczywistości liczbą. W tej liczbie znajdują się cyfry odwołujące się do godziny. W wielu przypadkach ten czas dla każdego wiersza to północ. Jeśli na przykład pole DateTimeKey w tabeli faktów Sprzedaż ma wartości takie jak 2010-10-19 12:00:00, oznacza to, że wartości są na poziomie dziennym dokładności. Jeśli wartości pola DateTimeKey zawierają godzinę, na przykład 2010-10-19 08:44:00, oznacza to, że wartości są na poziomie minuty dokładności. Wartości mogą być również na poziomie godziny dokładności, a nawet sekund precyzji. Poziom dokładności wartości czasu będzie miał istotny wpływ na sposób tworzenia tabeli dat oraz relacje między nią a tabelą faktów.
Musisz określić, czy dane zostaną zagregowane z dokładnością do dziennego, czy z dokładnością czasu. Innymi słowy, możesz użyć kolumn w tabeli dat, takich jak Poranek, Popołudnie lub Godzina, jako pól daty godziny w obszarach Wiersz, Kolumna lub Filtr tabeli przestawnej.
Uwaga: Dni to najmniejsza jednostka czasu, z którą mogą pracować funkcje analizy czasowej języka DAX. Jeśli nie musisz pracować z wartościami czasu, należy zmniejszyć dokładność danych, aby użyć dni jako jednostki minimalnej.
Jeśli dane mają zostać zagregowane do poziomu czasu, tabela dat będzie potrzebować kolumny daty z uwzględnioną godziną. W rzeczywistości będzie potrzebna kolumna daty z jednym wierszem na każdą godzinę, a może nawet każdą minutę każdego dnia, dla każdego roku w zakresie dat. Jest tak dlatego, że aby utworzyć relację między kolumną DateTimeKey w tabeli faktów a kolumną daty w tabeli dat, musisz mieć pasujące wartości. Jak można sobie wyobrazić, jeśli uwzględnisz wiele lat, może to sprawić, że będzie to bardzo duży stół z datami.
W większości przypadków jednak chcesz zagregować dane tylko do dnia. Innymi słowy kolumny, takie jak Rok, Miesiąc, Tydzień lub Dzień tygodnia, będą używane jako pola w obszarach Wiersz, Kolumna lub Filtr tabeli przestawnej. W tym przypadku kolumna daty w tabeli dat musi zawierać tylko jeden wiersz dla każdego dnia w roku, jak opisano wcześniej.
Jeśli kolumna daty zawiera dokładny poziom godziny, ale zostanie zagregowany tylko do poziomu dziennego, w celu utworzenia relacji między tabelą faktów a tabelą dat może być konieczne zmodyfikowanie tabeli faktów przez utworzenie nowej kolumny, która obcina wartości w kolumnie daty do wartości dnia. Innymi słowy, przekonwertuj wartość, taką jak 2010-10-19 08:44:00na2010-10-19 12:00:00. Następnie można utworzyć relację między tą nową kolumną a kolumną daty w tabeli dat, ponieważ wartości są zgodne.
Przyjrzyjmy się przykładowi. Ten obraz przedstawia kolumnę DateTimeKey w tabeli Fakt sprzedaży. Wszystkie agregacje danych w tej tabeli muszą być tylko na poziomie dnia, używając kolumn w tabeli daty kalendarza, takich jak Rok, Miesiąc, Kwartał itp. Godzina zawarta w wartości nie jest istotna, tylko rzeczywista data.
Ponieważ nie musimy analizować tych danych do poziomu czasu, kolumna Data w tabeli Data kalendarza nie musi zawierać jednego wiersza dla każdej godziny i każdej minuty każdego dnia w każdym roku. Kolumna Data w tabeli dat wygląda następująco:
Aby utworzyć relację między kolumną DateTimeKey w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz, możemy utworzyć nową kolumnę obliczeniową w tabeli Fakt sprzedaży i użyć funkcji TRUNC , aby obciąć wartość daty i godziny w kolumnie DateTimeKey do wartości daty zgodnej z wartościami w kolumnie Data w tabeli Kalendarz. Nasza formuła wygląda następująco:
=TRUNC([DateTimeKey],0)
Spowoduje to utworzenie nowej kolumny (o nazwie DateKey) zawierającej datę z kolumny DateTimeKey i godzinę 12:00:00 dla każdego wiersza:
Teraz możemy utworzyć relację między tą nową kolumną (DateKey) a kolumną Date w tabeli Kalendarz.
Podobnie możemy utworzyć kolumnę obliczeniową w tabeli Sprzedaż, która zmniejsza dokładność czasu w kolumnie DateTimeKey do poziomu godziny dokładności. W takim przypadku funkcja TRUNC nie będzie działać, ale nadal będziemy mogli używać innych funkcji data i godzina języka DAX, aby wyodrębnić i ponownie połączyć nową wartość z dokładnością godziny. Możemy użyć formuły w następujący sposób:
= DATA (ROK([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Nasza nowa kolumna wygląda następująco:
Pod warunkiem, że nasza kolumna Data w tabeli dat ma wartości do poziomu dokładności godziny, możemy następnie utworzyć relację między nimi.
Zwiększenie użyteczności dat
Wiele kolumn dat utworzonych w tabeli dat jest niezbędnych dla innych pól, ale w niektórych przypadkach nie jest to przydatne w analizie. Na przykład pole DateKey w tabeli Sprzedaż, do której odwoływaliśmy się i które przedstawiono w tym artykule, jest ważne, ponieważ dla każdej transakcji ta transakcja jest rejestrowana jako występująca w określonej dacie i godzinie. Jednak z punktu widzenia analizy i raportowania nie jest to aż tak przydatne, ponieważ nie można ich użyć jako pola wiersza, kolumny ani filtru w tabeli przestawnej lub raporcie.
Podobnie w naszym przykładzie kolumna Data w tabeli Kalendarz jest bardzo przydatna i istotna, ale nie można jej użyć jako wymiaru w tabeli przestawnej.
Aby tabele i kolumny w nich były jak najbardziej przydatne oraz aby ułatwić nawigowanie po listach pól tabeli przestawnej lub raportu programu Power View, należy ukryć niepotrzebne kolumny przed narzędziami klienta. Możesz również ukryć niektóre tabele. Pokazana wcześniej tabela Święta zawiera daty świąt, które są ważne dla niektórych kolumn w tabeli Kalendarz, ale nie można używać kolumn Data i Święta w tabeli Święta jako pól w tabeli przestawnej. W tym miejscu, aby ułatwić nawigowanie po listach pól, możesz ukryć całą tabelę Święta.
Innym ważnym aspektem pracy z datami jest nazewnictwo konwencji. W dodatku Power Pivot możesz dowolnie nazywać tabele i kolumny. Należy jednak pamiętać, zwłaszcza jeśli skoroszyt będzie udostępniany innym użytkownikom, dobra konwencja nazewnictwa ułatwia identyfikowanie tabel i dat nie tylko na listach pól, ale także w dodatku Power Pivot i formułach języka DAX.
Po utworzeniu tabeli dat w modelu danych możesz rozpocząć tworzenie miar, które pomogą Ci w jak największym wykorzystaniu możliwości danych. Niektóre mogą być tak proste, jak sumowanie sum sprzedaży w bieżącym roku, a inne mogą być bardziej złożone, gdzie trzeba filtrować według określonego zakresu unikatowych dat. Dowiedz się więcej w temacie Miary w dodatku Power Pivot i funkcjach analizy czasowej.
Dodatek
Konwertowanie dat typu danych tekstu na typ danych daty
W niektórych przypadkach tabela faktów z danymi transakcji może zawierać daty typu danych tekstowych. Oznacza to, że data wyświetlana jako 2012-12-04T11:47:09 w rzeczywistości nie jest datą, a przynajmniej nie jest typem daty zrozumiałej przez dodatek Power Pivot. To naprawdę tylko tekst, który brzmi jak data. Aby można było utworzyć relację między kolumną daty w tabeli faktów a kolumną daty w tabeli dat, obie kolumny muszą mieć typ danych Data .
Zazwyczaj podczas próby zmiany typu danych kolumny dat będącej typem danych tekstowych na typ danych daty dodatek Power Pivot może interpretować daty i automatycznie konwertować je na prawdziwy typ danych daty. Jeśli dodatek Power Pivot nie może tworzyć konwersji typów danych, zostanie wyświetlony błąd niezgodności typu.
Można jednak konwertować daty na rzeczywisty typ danych daty. Możesz utworzyć nową kolumnę obliczeniową i użyć formuły języka DAX, aby przeanalizować rok, miesiąc, dzień, godzinę itd. na podstawie ciągów tekstowych, a następnie połączyć ją z powrotem w sposób, w jaki dodatek Power Pivot może czytać jako prawdziwą datę.
W tym przykładzie zaimportowaliśmy tabelę faktów o nazwie Sprzedaż do dodatku Power Pivot. Zawiera kolumnę o nazwie DateTime. Wartości są wyświetlane następująco:
Jeśli przyjrzymy się typowi danych w grupie Formatowanie na karcie Narzędzia główne dodatku Power Pivot, zobaczymy, że jest to typ danych Tekst.
Nie można utworzyć relacji między kolumną DateTime a kolumną Date w tabeli dat, ponieważ typy danych nie są zgodne. Jeśli spróbujemy zmienić typ danych na Data, zostanie wyświetlony błąd niezgodności typu:
W takim przypadku dodatek Power Pivot nie może przekonwertować typu danych z tekstu na datę. Nadal możemy używać tej kolumny, ale aby uzyskać rzeczywisty typ danych daty, musimy utworzyć nową kolumnę, która analizuje tekst i ponownie tworzy go w wartości, a dodatek Power Pivot może utworzyć typ danych Data.
Pamiętaj, że w sekcji Praca z czasem we wcześniejszej części tego artykułu; jeśli analiza nie jest konieczna, należy przekonwertować daty w tabeli faktów na poziom dokładności dnia. Mając to na uwadze, chcemy, aby wartości w nowej kolumnie były na poziomie dokładności dnia (z wyłączeniem godziny). Możemy przekonwertować wartości w kolumnie DateTime na typ danych daty i usunąć poziom dokładności godziny za pomocą następującej formuły:
=DATA(LEWY([DateTime];4), FRAGMENT.DATY([DateTime];6;2); FRAGMENT.DATY([DateTime];9;2))
Spowoduje to utworzenie nowej kolumny (w tym przypadku o nazwie Data). Dodatek Power Pivot wykrywa nawet wartości, które mają być datami, i automatycznie ustawia typ danych na Data.
Jeśli chcemy zachować poziom dokładności czasu, po prostu rozszerzamy formułę o godziny, minuty i sekundy.
=DATA(LEWY([DateTime];4), FRAGMENT.DATY([DateTime];6;2); FRAGMENT.DATY([DateTime];9;2)) +
TIME(MID([DateTime];12;2), MID([DateTime],15,2), MID([DateTime],18;2))
Teraz, gdy mamy kolumnę Data typu danych Data, możemy utworzyć relację między nią a kolumną daty w dacie.
Dodatkowe zasoby
Obliczenia w dodatku Power Pivot
Szybki start: nauka podstaw języka DAX w 30 minut
