Tabele dat w dodatku Power Pivot są niezbędne do przeglądania i obliczania danych w czasie. Ten artykuł zawiera szczegółową wiedzę na temat tabel dat i sposobu ich tworzenia w dodatku Power Pivot. W szczególności artykuł ten opisuje:
- Powody, dla których tabela dat jest ważna do przeglądania i obliczania danych według dat i godzin.
- Jak dodać tabelę dat do modelu danych przy użyciu dodatku Power Pivot.
- Jak utworzyć nowe kolumny dat, takie jak rok, miesiąc i okres, w tabeli dat.
- Tworzenie relacji między tabelami dat a tabelami faktów.
- Jak pracować z czasem.
Ten artykuł jest przeznaczony dla użytkowników, którzy nie znają dodatku Power Pivot. Należy jednak pamiętać, że trzeba już dobrze rozumieć importowanie danych, tworzenie relacji oraz tworzenie kolumn obliczeniowych i miar.
W tym artykule nie opisano używania funkcji Time-Intelligence języka DAX w formułach miarowych. Aby uzyskać więcej informacji na temat tworzenia miar za pomocą funkcji analizy czasowej języka DAX, zobacz temat Analiza czasowa w dodatku Power Pivot w programie Excel.
Uwaga
W dodatku Power Pivot nazwy "miara" i "pole obliczeniowe" są synonimami. W tym artykule używamy miary nazwy. Aby uzyskać więcej informacji, zobacz temat Miary w dodatku Power Pivot.
Spis treści
Opis tabel dat
Niemal każda analiza danych obejmuje przeglądanie i porównywanie danych w oparciu o daty i godziny. Możesz na przykład zsumować kwoty sprzedaży z ostatniego kwartału obrachunkowego, a następnie porównać te sumy z innymi kwartałami lub obliczyć saldo zamknięcia miesiąca dla konta. W każdym z tych przypadków daty służą do grupowania i agregowania transakcji sprzedaży lub sald dla określonego okresu.
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 tworzenia fragmentów 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ędnionego w tabeli dat.
Jeśli na przykład dane, które chcesz przeglądać, mają daty od 1 lutego 2010 do 30 listopada 2012, a Twój raport dotyczy roku kalendarzowego, potrzebna będzie tabela dat z co najmniej zakresem dat od 1 stycznia 2010 do 31 grudnia 2012. Każdy rok w tabeli dat musi zawierać wszystkie dni dla każdego roku. Jeśli dane będą regularnie odświeżane przy użyciu nowszych danych, warto przesunąć datę końcową o rok lub dwa, aby nie trzeba było 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, a masz dane za lata obrachunkowe 2010 aż do daty bieżącej (na przykład w roku obrotowym 2013), możesz utworzyć tabelę dat, która rozpoczyna się od 2009-03-01 i zawiera przynajmniej każdy dzień w każdym roku obrachunkowym aż do ostatniej daty w roku obrachunkowym 2013.
Jeśli raportowanie będzie dotyczyło zarówno roku kalendarzowego, jak i roku obrachunkowego, nie trzeba tworzyć oddzielnych tabel dat. Pojedyncza tabela dat może zawierać kolumny dla roku kalendarzowego, roku obrachunkowego, a nawet trzynastu czterotygodniowych okresów. Istotne jest to, że tabela dat zawiera ciągły zestaw dat dla wszystkich lat z uwzględnieniem.
Dodawanie tabeli dat do modelu danych
Istnieje kilka sposobów dodawania tabeli dat do modelu danych:
- Zaimportuj z relacyjnej bazy danych lub innego źródła danych.
- Utwórz tabelę dat w programie Excel, a następnie skopiuj lub połącz nową tabelę w dodatku Power Pivot.
- Zaimportuj z witryny Microsoft Azure Marketplace.
Przyjrzyjmy się bliżej każdemu z nich.
Importowanie z relacyjnej bazy danych
W przypadku importowania części lub całości danych z hurtowni danych lub relacyjnej bazy danych innego typu istnieje duże prawdopodobieństwo, że istnieje już tabela dat i relacje między nią a pozostałymi importowanymi danymi. Daty i format prawdopodobnie będą zgodne z datami w danych faktów, a daty te prawdopodobnie zaczynają się w przeszłości i wybiegają daleko w przyszłość. Tabela dat, którą chcesz zaimportować, może być bardzo duża i zawierać zakres dat wykraczający poza to, co musisz uwzględnić w modelu danych. Zaawansowane funkcje filtrowania Kreatora importu tabel dodatku Power Pivot umożliwiają selektywne wybieranie tylko tych dat i kolumn, które są naprawdę potrzebne. 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ą one już istniały w zaimportowanej tabeli. Jednak w niektórych przypadkach po zaimportowaniu tabeli dat do modelu danych może być konieczne utworzenie dodatkowych kolumn dat w zależności od konkretnych potrzeb raportowania. Na szczęście można to łatwo zrobić przy użyciu 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 zawierają powiązaną tabelę dat lub kalendarzy, skontaktuj się z administratorem bazy danych.
Tworzenie tabeli dat 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żą elastyczność.
Tabela dat tworzona w programie Excel rozpoczyna się od jednej kolumny zawierającej ciągły zakres dat. Następnie można utworzyć dodatkowe kolumny, takie jak Rok, Kwartał, Miesiąc, Rok obrachunkowy, Okres itd., 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 zostało opisane w sekcji Dodawanie nowych kolumn dat do tabeli dat w dalszej części tego artykułu.
Instrukcje: tworzenie tabeli dat w programie Excel i kopiowanie jej do modelu danych
W programie Excel w pustym arkuszu w komórce A1 wpisz nazwę nagłówka kolumny identyfikującego zakres dat. Zazwyczaj będzie to coś w rodzaju Date, DateTime lub DateKey.
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 31.12.2016.
Zaznacz wszystkie wiersze w kolumnie Data (w tym 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 oknie Wklej podgląd>Nazwa tabeli wpisz nazwę, taką jak Data lub Kalendarz. Pozostaw zaznaczone pole wyboru Użyj pierwszego wiersza jako nagłówków kolumn, 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 także utworzyć za pomocą polecenia Dodaj do modelu danych. Powoduje to jednak niepotrzebny rozmiar skoroszytu, ponieważ zawiera on dwie wersje tabeli dat. jeden w programie Excel, a drugi w dodatku Power Pivot.
Uwaga
Data nazwy jest słowem kluczowym w dodatku Power Pivot. Jeśli nadasz nazwę tabeli utworzonej w dodatku Power Pivot Date, we wszystkich formułach języka DAX odwołujących się do niej w argumencie musisz ująć nazwę tabeli w pojedyncze cudzysłowy. Wszystkie przykładowe obrazy i formuły w tym artykule odwołują się do tabeli dat o nazwie Kalendarz utworzonej w dodatku Power Pivot.
W modelu danych mamy teraz tabelę dat. Przy użyciu języka DAX można dodać nowe kolumny z datami, takie jak rok, miesiąc itp.
Dodawanie nowych kolumn dat do tabeli dat
Tabela dat z jedną kolumną dat, która ma jeden wiersz na każdy dzień w każdym roku, jest ważna dla definiowania wszystkich dat w zakresie dat. Jest on również niezbędny do utworzenia relacji między tabelą faktów a tabelą dat. Jednak pojedyncza kolumna z datami zawierająca jeden wiersz na każdy dzień nie jest przydatna podczas analizowania według dat w tabeli przestawnej lub raporcie programu Power View. Chcesz, aby tabela dat zawierała kolumny ułatwiające agregowanie danych dla zakresu lub grupy dat. Można na przykład zsumować kwoty sprzedaży według miesiąca lub kwartału albo utworzyć miarę obliczającą wzrost rok do roku. W każdym z tych przypadków tabela dat wymaga kolumn roku, miesiąca lub kwartału, aby można było zagregować dane za dany okres.
Jeśli tabela dat została zaimportowana z relacyjnego źródła danych, może już zawierać różne typy kolumn dat, które chcesz wstawić. W niektórych przypadkach może być konieczne zmodyfikowanie niektórych z tych kolumn lub utworzenie dodatkowych kolumn z datami. 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 miałeś doświadczenia w używaniu języka DAX, świetnym miejscem do rozpoczęcia nauki jest przewodnik Szybki start: nauka podstaw języka DAX w 30 minut w Office.com.
Funkcje daty i godziny języka DAX
Jeśli masz okazję pracować z funkcjami daty i godziny w formułach programu Excel, prawdopodobnie znasz funkcje daty i godziny. Te funkcje są podobne do swoich odpowiedników w programie Excel, ale istnieją między nimi pewne istotne różnice:
- Funkcje daty i godziny języka DAX w języku DAX używają typu danych data/godzina.
- Mogą przyjmować wartości z kolumny jako argumenty.
- Za ich pomocą można zwracać wartości dat i/lub manipulować nimi.
Te funkcje są często używane podczas tworzenia niestandardowych kolumn dat w tabeli dat, dlatego ważne jest, aby je zrozumieć. Użyjemy kilku z tych funkcji, aby utworzyć kolumny dla roku, kwartału, miesiąca obrachunkowego i tak dalej.
Uwaga
Funkcje daty i godziny w języku DAX różnią się od funkcji analizy czasowej. Dowiedz się więcej na temat analizy czasowej w dodatku Power Pivot w programie Excel.
Język DAX zawiera następujące funkcje daty i godziny:
- DATA
- DATA.WARTOŚĆ
- NASTĘPNY DZIEŃ
- NR.SER.DATY
- NR.SER.OST.DN.MIES
- HOUR (godzina)
- MINUTE (minuta)
- MONTH (miesiąc)
- TERAZ
- SECOND (sekunda)
- TIME
- CZAS.WARTOŚĆ
- DZIŚ
- DZIEŃ.TYG
- NUM.TYG
- YEAR (rok)
- CZĘŚĆ.ROKU
W formułach można również używać wielu innych funkcji języka DAX. Na przykład w wielu opisanych tutaj formułach użyto funkcji matematycznych i trygonometrycznych , takich jak MOD i LICZBA, funkcji logicznych , takich jak JEŻELI, oraz funkcji tekstowych , takich 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 na tej podstawie zwrócić rok.
Miesiąc
=MIESIĄC([data])
W tej formule, podobnie jak w przypadku funkcji ROK, możemy 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 użyjemy funkcji ZAOKR.DO.CAŁK , aby zwrócić wartość daty w postaci liczby całkowitej. Argument określony dla funkcji ZAOKR.DO.CAŁK to wartość z kolumny Miesiąc, dodaj 2, a następnie podziel ją przez 3, aby uzyskać wartość kwartału od 1 do 4.
Nazwa miesiąca
=FORMAT([data];"mmmm")
Aby uzyskać nazwę miesiąca, w tej formule użyj funkcji FORMAT w celu przekonwertowania wartości liczbowej 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żyjemy ciągu "mmmm". Nasz wynik wygląda następująco:
Jeśli chcemy, aby nazwa miesiąca skrócona do trzech liter była zwracana, należy użyć "mmm" jako argumentu format.
Dzień tygodnia
=FORMAT([data],"ddd")
W tej formule użyjemy funkcji FORMAT, aby uzyskać nazwę dnia. Ponieważ chcemy tylko skróconej nazwy dnia, podajemy "ddd" w argumencie format.
Przykładowa tabela przestawna
Gdy masz pola z datami, takimi jak rok, kwartał, miesiąc itp., możesz ich użyć w tabeli przestawnej lub raporcie. Na przykład na poniższej ilustracji przedstawiono pole KwotaSprzedaży z tabeli faktów Sprzedaż w tabeli WARTOŚCI oraz pole Rok i Kwartał z tabeli wymiaru Kalendarz w tabeli WIERSZE. Pole KwotaSprzedaży jest agregowane dla kontekstu 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 istnieje funkcja, która mogłaby wyodrębnić rok obrachunkowy z wartości daty, ponieważ daty początkowe i końcowe roku obrachunkowego często różnią się od dat roku kalendarzowego. Aby uzyskać rok obrachunkowy, najpierw za pomocą funkcji JEŻELI sprawdzimy, czy wartość kolumny Miesiąc jest mniejsza niż lub równa 6. W drugim argumencie, jeśli wartość Miesiąc jest mniejsza niż lub równa 6, zwróć wartość z kolumny Rok. Jeśli nie, zwróć wartość z kolumny Year i dodaj 1.
Innym sposobem określenia wartości miesiąca na koniec roku obrachunkowego jest utworzenie miary, która po prostu określa miesiąc. Na przykład FYE:=6. Następnie zamiast numeru miesiąca można odwołać się do nazwy miary. Na przykład =JEŻELI([Miesiąc]<=[RO];[Rok];[Rok]+1). Zapewnia to większą elastyczność podczas odwoływania się do miesiąca zakończenia 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ść dla [Miesiąc] jest mniejsza niż lub równa 6, a następnie przyjmujemy 6 i dodajemy wartość z Miesiąc, w przeciwnym razie odejmujemy 6 od wartości z [Miesiąc].
Kwartał obrachunkowy
=INT(([MiesiącObrachunkowy]+2)/3)
Formuła używana dla funkcji Kwartał obrachunkowy jest bardzo podobna do formuły dla kwartału w naszym roku kalendarzowym. Jedyna różnica polega na tym, że podajemy [FiscalMonth] zamiast [Month].
Święta i daty specjalne
Możesz chcieć dołączyć kolumnę z datami, która wskazuje, że określone daty są świętami lub innymi szczególnymi datami. Możesz na przykład zsumować sumy sprzedaży w Nowym Roku, dodając pole Święto do tabeli przestawnej, jako fragmentator lub filtr. W innych przypadkach można chcieć wykluczyć te daty z innych kolumn dat lub z miary.
Uwzględnianie świąt lub specjalnych dni jest dość proste. W programie Excel możesz utworzyć tabelę zawierającą daty, które chcesz uwzględnić. Następnie możesz go skopiować lub użyć polecenia Dodaj do modelu danych, aby dodać go do modelu danych jako tabelę połączoną. W większości przypadków nie trzeba tworzyć relacji między tabelą a tabelą programu Calendar. Wszystkie formuły odwołujące się do niej mogą używać funkcji WYSZUKAJ WARTOŚĆ w celu zwrócenia wartości.
Poniżej znajduje się przykład tabeli utworzonej w programie Excel, która zawiera dni wolne dodawane do tabeli dat:
| Data | Święto |
|---|---|
| 1/1/2010 | Nowy Rok |
| 11/25/2010 | Święto Dziękczynienia |
| 12/25/2010 | Boże Narodzenie |
| 2011-01-01 | Nowy Rok |
| 11/24/2011 | Święto Dziękczynienia |
| 12/25/2011 | Boże Narodzenie |
| 2012-01-01 | Nowy Rok |
| 2012-11-22 | Święto Dziękczynienia |
| 12/25/2012 | Boże Narodzenie |
| 1/1/2013 | Nowy Rok |
| 11/28/2013 | Święto Dziękczynienia |
| 12/25/2013 | Boże Narodzenie |
| 11/27/2014 | Święto Dziękczynienia |
| 12/25/2014 | Boże Narodzenie |
| 2014-01-01 | Nowy Rok |
| 11/27/2014 | Święto Dziękczynienia |
| 12/25/2014 | Boże Narodzenie |
| 1/1/2015 | Nowy Rok |
| 11/26/2014 | Święto Dziękczynienia |
| 12/25/2015 | Boże Narodzenie |
| 2016-01-01 | Nowy Rok |
| 11/24/2016 | Święto Dziękczynienia |
| 12/25/2016 | Boże Narodzenie |
W tabeli dat tworzymy kolumnę o nazwie Święta i używamy następującej formuły:
=WYSZUKAJ WARTOŚĆ(Święta[Święta],Święta[data],Kalendarz[data])
Przyjrzyjmy się bliżej tej formule.
Funkcja SZUKAJ.WARTOŚĆ służy do pobierania wartości z kolumny Święta w tabeli Święta. W pierwszym argumencie określamy kolumnę, w której będzie nasza wartość wynikowa. Określamy kolumnę Święta w tabeli Święta , ponieważ taka wartość ma zostać zwrócona.
=WYSZUKAJ WARTOŚĆ(Święta[Święta],Święta[data],Kalendarz[data])
Następnie określamy drugi argument, kolumnę wyszukiwania zawierającą daty, które chcemy wyszukać. Kolumnę Data określamy w tabeli Święta w następujący sposób:
=WYSZUKAJ WARTOŚĆ(Święta[Święta],Święta[data],Kalendarz[data])
Na koniec określam kolumnę w tabeli Calendar, zawierającą daty, które chcemy wyszukać w tabeli Holiday. Jest to oczywiście kolumna Data w tabeli Kalendarz.
=WYSZUKAJ.WARTOŚĆ(Święta[Święta],Święta[data],Kalendarz[data])
Kolumna Dzień wolny zwróci nazwę dnia wolnego dla każdego wiersza zawierającego wartość daty zgodną z datą w tabeli Święta.
Kalendarz niestandardowy — trzynaście czterotygodniowych okresów
Niektóre organizacje, takie jak handel detaliczny lub gastronomia, często raportują różne okresy, na przykład trzynaście czterotygodniowych okresów. W kalendarzu trzynastoczterotygodniowym każdy okres ma 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 zazwyczaj święta przypadają w tym samym okresie każdego roku. Możesz rozpocząć miesiączkę w dowolnym dniu tygodnia. Podobnie jak w przypadku dat w kalendarzu lub roku obrachunkowym, możesz użyć języka DAX do tworzenia dodatkowych kolumn z datami niestandardowymi.
W poniższych przykładach pierwszy pełny okres rozpoczyna się w pierwszą niedzielę roku obrachunkowego. W takim przypadku rok obrachunkowy zaczyna się 1.07.
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ę, a więc pierwszy pełny tydzień pierwszego roku obrachunkowego w tabeli Kalendarz faktycznie rozpoczyna się 2010-07-04 i trwa do ostatniego pełnego tygodnia w tabeli Kalendarz. Chociaż ta wartość sama w sobie nie jest aż tak przydatna w analizie, konieczne jest obliczenie na potrzeby użycia w innych formułach na okres 28-dniowy.
=INT([data]-40356)/7)
Przyjrzyjmy się bliżej tej formule.
Najpierw utworzymy formułę zwracającą wartości z kolumny Data w postaci liczby całkowitej, na przykład:
=INT([data])
Następnie chcemy poszukać pierwszej niedzieli w pierwszym roku podatkowym. Widzimy, że jest 04.07.2010.
Teraz odejmij liczbę 40356 (która jest liczbą całkowitą dla daty 2010-06-27, ostatniej niedzieli poprzedniego roku obrachunkowego) od tej wartości, aby uzyskać liczbę dni od początku dni w tabeli Kalendarz w następujący sposób:
=INT([data]-40356)
Następnie podziel wynik przez 7 (dni w tygodniu) w następujący sposób:
=INT(([data]-40356)/7)
Wynik wygląda następująco:
Period
Okres w tym kalendarzu niestandardowym zawiera 28 dni i zawsze rozpoczyna się w niedzielę. Ta kolumna zwróci numer okresu, rozpoczynając się od pierwszej niedzieli pierwszego roku obrachunkowego.
=INT(([Tydzień]+3)/4)
Przyjrzyjmy się bliżej tej formule.
Najpierw utworzymy formułę zwracającą wartość z kolumny Tydzień w postaci liczby całkowitej, na przykład:
= INT([Tydzień])
Następnie dodaj 3 do tej wartości w następujący sposób:
=INT([Tydzień]+3)
Następnie podziel wynik przez 4 w następujący sposób:
=INT(([Tydzień]+3)/4)
Wynik wygląda następująco:
Rok obrachunkowy okresu
Ta wartość zwraca rok obrachunkowy okresu.
=INT(([Okres]+12)/13)+2008
Przyjrzyjmy się bliżej tej formule.
Najpierw utworzymy formułę, która zwróci wartość z tabeli Okres i doda liczbę 12:
=([Okres]+12)
Wynik dzielimy przez 13, ponieważ rok obrachunkowy ma trzynaście okresów po 28 dni:
=(([Okres]+12)/13)
Dodajemy rok 2010, ponieważ jest to pierwszy rok w tabeli:
=(([Okres]+12)/13)+2010
Na koniec użyj funkcji ZAOKR.DO.CAŁK, 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 w roku obrachunkowym
Ta wartość zwraca numer okresu (od 1 do 13), rozpoczynając od pierwszego pełnego okresu (rozpoczynającego się w niedzielę) w każdym roku obrachunkowym.
=JEŻELI(MOD([okres];13);MOD([okres];13);13)
Ta formuła jest nieco bardziej złożona, dlatego najpierw opiszemy ją w języku, który lepiej rozumiemy. Ta formuła mówi, że należy podzielić wartość z [okresu] przez 13, aby uzyskać numer okresu (1-13) w roku. Jeśli ta liczba jest równa 0, zwróć wartość 13.
Najpierw tworzymy formułę, która zwraca resztę wartości z pola Okres o 13. Możemy użyć MOD (funkcje matematyczne i trygonometryczne) w następujący sposób:
= MOD([Okres];13)
Daje to w większości żądany wynik, z wyjątkiem sytuacji, gdy wartość Period wynosi 0, ponieważ te daty nie przypadają w pierwszym roku obrachunkowym, tak jak w pierwszych pięciu dniach naszej przykładowej tabeli dat kalendarza. Można to zrobić za pomocą funkcji JEŻELI. Jeśli nasz wynik wynosi 0, zwrócimy liczbę 13 w następujący sposób:
= JEŻELI(MOD([okres];13);MOD([okres];13);13)
Wynik wygląda następująco:
Przykładowa tabela przestawna
Poniższy obraz przedstawia tabelę przestawną z pola KwotaSprzedaży z tabeli faktów Sprzedaż w VALUES oraz pola PeriodFiscalYear i PeriodInFiscalYear z tabeli wymiarów daty Calendar w ROWS. Pole KwotaSprzedaży jest agregowane dla kontekstu według roku obrachunkowego i 28-dniowego okresu roku obrachunkowego.
Relacje
Aby po utworzeniu tabeli dat w modelu danych 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 z danymi transakcji a tabelą dat.
Ponieważ musisz utworzyć relację na podstawie dat, musisz upewnić się, że została utworzona relacja między kolumnami o typie danych data/godzina (Data).
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ów Sprzedaż z wartością 8/15/2012 12:00 AM w kolumnie DateKey musi mieć odpowiadającą wartość w powiązanej kolumnie Data w tabeli dat (o nazwie Calendar). Jest to jeden z najważniejszych powodów, dla których chcesz, aby kolumna dat w tabeli dat zawierała ciągły zakres dat uwzględniających wszelkie możliwe daty w tabeli faktów.
Uwaga
Chociaż kolumna daty w każdej tabeli musi mieć ten sam typ danych (Data), format każdej kolumny nie ma znaczenia.
Uwaga
Jeśli dodatek Power Pivot nie pozwala na utworzenie relacji między dwiema tabelami, pola daty mogą nie przechowywać daty i godziny z takim samym poziomem dokładności. W zależności od formatowania kolumn wartości mogą wyglądać tak samo, ale być przechowywane inaczej. Dowiedz się więcej o pracy z czasem.
Uwaga
Unikaj używania w relacjach kluczy zastępczych liczb całkowitych. Podczas importowania danych z relacyjnego źródła danych często kolumny dat i godzin są reprezentowane przez klucz zastępczy, czyli kolumnę liczb całkowitych używaną do reprezentowania unikatowej daty. W dodatku Power Pivot należy unikać tworzenia relacji przy użyciu kluczy daty/godziny z liczbami całkowitymi, zamiast tego używać kolumn zawierających unikatowe wartości z typem danych daty. Mimo że używanie kluczy zastępczych jest uważane za najlepsze rozwiązanie w tradycyjnych hurtowniach danych, klucze liczb całkowitych 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 wystąpi błąd niezgodności typu, 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 typu bez daty (zwykle typu tekstowego) na typ danych daty. Nadal można używać tej kolumny w tabeli faktów, ale konieczne będzie przekonwertowanie danych za pomocą formuły języka DAX do nowej kolumny obliczeniowej. Zobacz sekcję Konwertowanie dat typu danych tekstowych na typy danych data w dalszej części dodatku.
Wiele relacji
W niektórych przypadkach może być konieczne utworzenie wielu relacji lub wielu tabel dat. Jeśli na przykład w tabeli faktów Sprzedaż istnieje wiele pól daty, takich jak DateKey, ShipDate i ReturnDate, wszystkie one mogą mieć relacje z polem Data w tabeli dat kalendarza Calendar, ale tylko jedno z nich może być aktywną relacją. W tym przypadku pole KluczDaty oznacza datę transakcji, a więc datę najważniejszą, dlatego ta relacja najlepiej sprawdza się jako aktywna relacja. Pozostałe mają nieaktywne relacje.
Poniższa tabela przestawna służy do obliczania łącznej sprzedaży według roku obrachunkowego i kwartału obrachunkowego. Miara o nazwie Total Sales, z formułą Total Sales:=SUM([SalesAmount]), jest umieszczana w WARTOŚCIACH, a pola FiscalYear i FiscalQuarter z tabeli dat kalendarza (Calendar) są umieszczane w ROWS.
Ta prosta tabela przestawna działa poprawnie, ponieważ chcemy zsumować naszą łączną sprzedaż przez wartość transactiondate w polu KluczDaty. Nasza miara Total Sales używa dat w tabeli DateKey i jest sumowana według roku obrachunkowego i kwartału obrachunkowego, ponieważ istnieje relacja między kolumną DateKey w tabeli Sales a kolumną Date w tabeli dat Calendar.
Nieaktywne relacje
A co, jeśli chcemy zsumować naszą całkowitą sprzedaż nie według daty transakcji, ale według daty wysyłki? Potrzebna jest relacja między kolumną DataWysyłki w tabeli Sprzedaż a kolumną Data w tabeli Calendar. Jeśli nie utworzymy tej relacji, nasze agregacje będą zawsze oparte na dacie transakcji. Jednak może istnieć wiele relacji, chociaż tylko jedna może być aktywna, a ponieważ data transakcji jest najważniejsza, otrzymuje ona aktywną relację z tabelą Kalendarz.
W tym przypadku do kolumny DataWysyłki istnieje relacja nieaktywna, 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 .
Ponieważ na przykład istnieje nieaktywna relacja między kolumną DataWysyłki w tabeli Sprzedaż a kolumną Data w tabeli Calendar, można utworzyć miarę sumującą sumę sprzedaży według daty wysyłki. Użyto następującej formuły do określenia zależności do użycia:
Łączna sprzedaż według daty wysyłki:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ta formuła stwierdza po prostu: Oblicz sumę dla kolumny KwotaSprzedaży, filtrując przy użyciu relacji między kolumną DataWysyłki w tabeli Sprzedaż a kolumną Data w tabeli Calendar.
Jeśli natomiast utworzysz tabelę przestawną i umieścimy miarę Total Sales by Ship Date w pozycji VALUES, a miarę Rok obrachunkowy i Kwartał obrachunkowy w ROWS, otrzymamy tę samą sumę końcową, ale wszystkie inne kwoty sum dla roku obrachunkowego i kwartału obrachunkowego różnią się, ponieważ są oparte na dacie wysyłki, a nie na dacie transakcji.
W przypadku relacji nieaktywnych można używać tylko jednej tabeli dat, ale wszelkie miary (takie jak Total Sales by Ship Date) muszą odwoływać się w formule do tej nieaktywnej relacji. Istnieje inne rozwiązanie, które polega na korzystaniu z wielu tabel dat.
Wiele tabel dat
Innym sposobem pracy z wieloma kolumnami dat w tabeli faktów jest utworzenie wielu tabel dat i utworzenie oddzielnych aktywnych relacji między nimi. Przyjrzyjmy się ponownie przykładowej tabeli Sprzedaż. Mamy trzy kolumny z datami, w których możemy chcieć zagregować dane:
- Klucz daty z datą sprzedaży dla każdej transakcji.
- A ShipDate — z datą i godziną wysyłki sprzedanych towarów do klienta.
- ReturnDate — z datą i godziną otrzymania co najmniej jednego zwracanego elementu.
Pamiętaj, że najważniejsze jest pole KluczDaty z datą transakcji. Większość agregacji będziemy wykonywać na podstawie tych dat, dlatego z pewnością będzie potrzebna relacja między nimi a kolumną Data w tabeli Kalendarz. Jeśli nie chcemy tworzyć nieaktywnych relacji między kolumnami DataWysyłki i DataZwrotu, Calendar a tym samym wymagać specjalnych formuł miar, możemy utworzyć dodatkowe tabele dat dla daty wysyłki i daty zwrotu. Możemy wtedy tworzyć aktywne relacje między nimi.
W tym przykładzie utworzyliśmy kolejną tabelę dat o nazwie ShipCalendar. Oczywiście oznacza to również utworzenie dodatkowych kolumn dat, a ponieważ te kolumny dat znajdują się w innej tabeli dat, chcemy nadać im nazwy w sposób odróżniający je od tych samych kolumn w tabeli Kalendarz. Utworzyliśmy na przykład kolumny o nazwach RokOdbiorcy, MiesiącOdbiorcy, KwartałOdbiorcy i tak dalej.
Jeśli utworzę tabelę przestawną i umieszczę miarę Total Sales w VALUES, a miary ShipFiscalYear i ShipFiscalQuarter w ROWS, zostaną wyświetlone te same wyniki, które były widoczne po utworzeniu 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 przechodzących przez nieaktywne relacje przy użyciu funkcji USERELATIONSHIP. Z drugiej strony tworzenie wielu tabel dat na liście pól może być kłopotliwe, a ponieważ w modelu danych jest więcej tabel, wymaga to więcej pamięci. Poeksperymentuj z tym, co działa najlepiej.
Właściwość Tabela dat
Właściwość Tabela dat ustawia metadane niezbędne do prawidłowego działania funkcji Time-Intelligence, takich jak TOTALYTD, PREVIOUSMONTH i DATESBETWEEN. Gdy obliczenia są wykonywane przy użyciu jednej z tych funkcji, aparat formuły dodatku Power Pivot wie, gdzie pobierać potrzebne daty.
Ostrzeżenie
Jeśli ta właściwość nie jest ustawiona, miary używające funkcji Time-Intelligence języka DAX mogą nie zwracać prawidłowych wyników.
Ustawiając właściwość Tabela dat, określasz w niej tabelę dat oraz kolumnę dat o typie danych Data (data/godzina).
Jak ustawić właściwość Tabela dat
- W oknie dodatku PowerPivot wybierz tabelę Kalendarz.
- Na karcie Projektowanie kliknij pozycję Oznacz jako tabelę dat.
- W oknie dialogowym Oznaczanie jako tabeli dat wybierz kolumnę z unikatowymi wartościami i typem danych Data.
Praca z czasem
Wszystkie wartości dat o typie danych Data w programie Excel lub programie SQL Server są w rzeczywistości liczbami. Liczba ta zawiera cyfry odnoszące się do czasu. W wielu przypadkach godziną dla każdego wiersza jest północ. Jeśli na przykład pole KluczDatyGodziny w tabeli faktów dotyczących sprzedaży ma wartości takie jak 10/19/2010 12:00:00 AM, oznacza to, że wartości odpowiadają dziennemu poziomowi dokładności. Jeśli wartości pola DateTimeKey zawierają godzinę, na przykład 2010-10-19 8:44:00, oznacza to, że wartości są zgodne z dokładnością co do minuty. Wartości mogą być również na poziomie dokładności na poziomie godziny, a nawet sekund. Poziom precyzji wartości czasu będzie miał istotny wpływ na sposób tworzenia tabeli dat i relacji między nią a tabelą faktów.
Należy określić, czy dane będą agregowane na poziomie dokładności dnia, czy 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, musisz zmniejszyć dokładność danych, aby używać dni jako jednostki minimalnej.
Jeśli zamierzasz agregować dane do poziomu czasu, tabela dat musi zawierać kolumnę z godziną. W rzeczywistości będzie potrzebować kolumny dat z jednym wierszem reprezentującym każdą godzinę, a może nawet minutę każdego dnia, dla każdego roku w zakresie dat. Jest tak, ponieważ aby utworzyć relację między kolumną KluczDatyGodziny w tabeli faktów a kolumną daty w tabeli dat, muszą istnieć zgodne wartości. Jak możesz sobie wyobrazić, jeśli uwzględnisz wiele lat, może to spowodować powstanie bardzo dużej tabeli dat.
Jednak w większości przypadków chcesz zagregować dane tylko do jednego dnia. Innymi słowy będziesz używać kolumn, takich jak Rok, Miesiąc, Tydzień lub Dzień tygodnia, jako pól w obszarach Wiersz, Kolumna lub Filtr tabeli przestawnej. W takim przypadku kolumna daty w tabeli dat musi zawierać tylko jeden wiersz dla każdego dnia w roku, jak opisano powyżej.
Jeśli kolumna daty zawiera określony poziom dokładności czasu, ale chcesz agregować tylko do poziomu dnia, 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 dat do wartości dnia. Innymi słowy, należy przekonwertować wartość, taką jak 2010-10-19 08:44:00 na 2010-10-19 12:00:00. Następnie możesz utworzyć relację między tą nową kolumną a kolumną daty w tabeli dat, ponieważ wartości są zgodne.
Spójrzmy na przykład. Na tym obrazie przedstawiono kolumnę KluczDatyGodziny w tabeli faktów dotyczących sprzedaży. Wszystkie agregacje danych w tej tabeli muszą dotyczyć tylko poziomu dni, przy użyciu kolumn w tabeli dat Calendar, takich jak Rok, Miesiąc, Kwartał itp. Czas uwzględniony w wartości nie jest istotny, tylko rzeczywista data.
Ponieważ nie trzeba analizować tych danych do poziomu czasu, kolumna Data w tabeli dat kalendarza kalendarza nie musi zawierać jednego wiersza dla każdej godziny i każdej minuty każdego dnia w roku. Kolumna Data w tabeli dat wygląda następująco:
Aby utworzyć relację między kolumną DateTimeKey w tabeli Sales i kolumną Data w tabeli Calendar, można utworzyć nową kolumnę obliczeniową w tabeli faktów Sales i przy użyciu funkcji TRUNC obciąć wartość daty i godziny w kolumnie DateTimeKey na wartość daty zgodną z wartościami w kolumnie Data w tabeli Calendar. Nasza formuła wygląda następująco:
=TRUNC([DateTimeKey],0)
Spowoduje to dodanie nowej kolumny (nazwanej DateKey) zawierającej datę z kolumny DateTimeKey oraz godzinę 12:00:00 dla każdego wiersza:
Teraz można utworzyć relację między tą nową kolumną (DateKey) a kolumną Date w tabeli Calendar.
Podobnie można utworzyć kolumnę obliczeniową w tabeli Sales, która zmniejsza dokładność czasu w kolumnie DateTimeKey do poziomu dokładności godziny. W tym przypadku funkcja TRUNC nie zadziała, ale można nadal użyć innych funkcji daty i godziny języka DAX języka DAX w celu wyodrębnienia i ponownego połączenia nowej wartości z godzinowym poziomem dokładności. Możemy użyć następującej formuły:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey])) + TIME (HOUR([DateTimeKey]), 0, 0)
Nasza nowa kolumna wygląda następująco:
Jeśli kolumna Data w tabeli dat ma wartości na poziomie dokładności godziny, można utworzyć relację między nimi.
Zwiększanie użyteczności dat
Wiele z kolumn dat tworzonych w tabeli dat jest potrzebnych dla innych pól, ale tak naprawdę nie są one aż tak przydatne w analizie. Na przykład pole KluczDaty, o której wspominaliśmy i którą pokazaliśmy w tym artykule, jest ważne, ponieważ dla każdej transakcji transakcja jest rejestrowana jako występująca w określonym dniu i godzinie. Jednak z punktu widzenia analizy i raportowania nie jest on zbyt przydatny, ponieważ nie można go używać jako pola wiersza, kolumny lub filtru w tabeli przestawnej lub raporcie.
Podobnie w naszym przykładzie kolumna Data w tabeli Calendar jest bardzo przydatna, krytyczna, ale nie można jej użyć jako wymiaru w tabeli przestawnej.
Aby tabele i zawarte w nich kolumny były jak najbardziej użyteczne, a także 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 także ukryć niektóre tabele. Pokazana wcześniej tabela Dni wolne zawiera daty dni wolnych, które są ważne dla określonych kolumn tabeli kalendarza, ale nie można użyć samych kolumn Data i Dzień wolny w tabeli Święta jako pól w tabeli przestawnej. Również w tym przypadku w celu ułatwienia nawigowania po listach pól można ukryć całą tabelę Święta.
Innym ważnym aspektem pracy z datami są konwencje nazewnictwa. W dodatku Power Pivot tabele i kolumny można nazywać w dowolny sposób. Warto jednak pamiętać, że odpowiednia konwencja nazewnictwa ułatwia identyfikowanie tabel i dat nie tylko na listach pól, ale również w dodatku Power Pivot i formułach języka DAX, zwłaszcza w przypadku udostępniania skoroszytu innym użytkownikom.
Po utworzeniu tabeli dat w modelu danych możesz zacząć tworzyć miary, które ułatwią optymalne wykorzystanie danych. Niektóre mogą być tak proste, jak suma sprzedaży w bieżącym roku, a inne mogą być bardziej złożone i wymagać filtrowania według określonego zakresu unikatowych dat. Dowiedz się więcej z tematów Miary w dodatku Power Pivot i funkcjach analizy czasowej.
Dodatek
Konwertowanie typu danych tekstowych dat na typ danych daty
W niektórych przypadkach tabela faktów z danymi transakcji może zawierać daty typu tekstowego. Oznacza to, że data wyświetlana jako 2012-12-04T11:47:09 w rzeczywistości wcale nie jest datą, a przynajmniej nie jest typem daty akceptowanym przez dodatek Power Pivot. Tak naprawdę jest to tylko tekst odczytywany 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 .
Zwykle podczas próby zmiany typu danych dla kolumny dat z typem danych tekstowych na typ danych data dodatek Power Pivot może interpretować daty i automatycznie konwertować je na typ danych data prawda. Jeśli dodatek Power Pivot nie może przeprowadzić konwersji typu danych, wystąpi błąd niezgodności typu.
Można jednak nadal konwertować daty na rzeczywiste typy danych. Możesz utworzyć nową kolumnę obliczeniową i użyć formuły języka DAX w celu przeanalizowania roku, miesiąca, dnia, godziny itd. na podstawie ciągów tekstowych, a następnie połączyć je z powrotem w sposób, który dodatek Power Pivot może odczytać jako rzeczywistą datę.
W tym przykładzie zaimportowaliśmy tabelę faktów o nazwie Sprzedaż do dodatku Power Pivot. Zawiera kolumnę o nazwie DateTime (Data/godzina). Wartości są wyświetlane w następujący sposób:
Jeśli spojrzymy na typ 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 kolumnami Data/godzina i Data w tabeli dat, ponieważ typy danych nie są zgodne. Jeśli spróbuję zmienić typ danych na Data, zostanie wyświetlony błąd niezgodności typu:
W tym przypadku dodatek Power Pivot nie mógł przekonwertować typu danych z tekstu na datę. Można nadal używać tej kolumny, ale w celu uzyskania dla niej danych typu data rzeczywista należy utworzyć nową kolumnę, która przeanalizuje tekst i ponownie utworzy z niego wartość. Dodatek Power Pivot może utworzyć typ danych Data.
Pamiętaj, że z sekcji Praca z czasem we wcześniejszej części tego artykułu; Jeśli przeprowadzanie analizy na poziomie dokładności pora dnia nie jest konieczne, należy przekonwertować daty w tabeli faktów na poziom dokładności dni. Mając to na uwadze, chcemy, aby wartości w nowej kolumnie były na poziomie dokładności dnia (bez godziny). Możemy zarówno przekonwertować wartości w kolumnie DateTime na typ danych date, jak i usunąć poziom precyzji czasu za pomocą następującej formuły:
=DATA(LEWY([Data_godzina];4); FRAGMENT.TEKSTU([Data_godzina];6;2); FRAGMENT.TEKSTU([Data_godzina];9;2))
Spowoduje to utworzenie nowej kolumny (w tym przypadku o nazwie Data). Dodatek Power Pivot wykrywa nawet, że wartości są datami, i automatycznie ustawia typ danych na Data.
Aby zachować poziom dokładności czasu, wystarczy rozszerzyć formułę, aby uwzględniała godziny, minuty i sekundy.
=DATA(LEWY([Data_godzina];4); FRAGMENT.TEKSTU([Data_godzina];6;2), FRAGMENT.TEKSTU([Data_godzina];9;2)) +
CZAS(FRAGMENT.TEKSTU([Data_godzina];12;2); FRAGMENT.TEKSTU([Data_godzina];15;2); FRAGMENT.TEKSTU([Data_godzina];18;2))
Teraz, gdy mam kolumnę danych typu 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