Analiza czasowa w dodatku Power Pivot w programie Excel

Dotyczy
Excel dla Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Język DAX (Data Analysis Expressions) zawiera 35 funkcji przeznaczonych do agregowania i porównywania danych w czasie. W przeciwieństwie do funkcji daty i godziny języka DAX funkcje analizy czasowej nie mają nic podobnego w programie Excel. Jest to spowodowane tym, że funkcje analizy czasowej pracują z danymi, które podlegają ciągłym zmianom w zależności od kontekstu wybranego w tabelach przestawnych i wizualizacjach programu Power View.

Aby pracować z funkcjami analizy czasowej, w modelu danych musi być uwzględniona tabela dat. Tabela dat musi zawierać kolumnę z jednym wierszem dla każdego dnia każdego roku uwzględnionego w danych. Ta kolumna jest traktowana jako kolumna Data (chociaż można ją nazwać w dowolny sposób). Wiele funkcji analizy czasowej wymaga kolumny z datą, aby móc wykonywać obliczenia zgodnie z datami wybranymi jako pola w raporcie. Na przykład jeśli istnieje miara, która oblicza saldo na koniec kwartału zamknięcia przy użyciu funkcji CLOSESINGBALANCEQTR, to aby dodatek Power Pivot mógł ustalić, kiedy w rzeczywistości jest koniec kwartału, musi odwoływać się do kolumny z datą w tabeli dat w celu określenia daty początku i końca kwartału. Aby dowiedzieć się więcej o tabelach dat, zobacz Opis i tworzenie tabel dat w dodatku Power Pivot w programie Excel.

Funkcje

Funkcje zwracające jedną datę

Funkcje z tej kategorii zwracają jedną datę. Uzyskane wyniki mogą być następnie używane jako argumenty innych funkcji.

Pierwsze dwie funkcje z tej kategorii zwracają pierwszą lub ostatnią datę w Date_Column w bieżącym kontekście. Może to być przydatne, gdy chcesz znaleźć pierwszą lub ostatnią datę transakcji określonego typu. Te funkcje przyjmują tylko jeden argument — nazwę kolumny dat w tabeli dat.

Kolejne dwie funkcje z tej kategorii służą do znajdowania pierwszej lub ostatniej daty (lub dowolnej innej wartości w kolumnie), w której wyrażenie nie jest puste. Jest to najczęściej stosowane w sytuacjach, takich jak spisy, kiedy chcesz uzyskać ostatnią ilość zapasów, ale nie wiesz, kiedy to zrobiono.

Sześć innych funkcji, które zwracają jedną datę, to funkcje zwracające pierwszą lub ostatnią datę miesiąca, kwartału lub roku w bieżącym kontekście obliczeń.

Funkcje zwracające tabelę dat

Istnieje szesnaście funkcji analizy czasowej, które zwracają tabelę dat. Najczęściej te funkcje są używane jako argumenty UstawFiltr dla funkcji CALCULATE . Podobnie jak wszystkie funkcje analizy czasowej w języku DAX, każda funkcja przyjmuje kolumnę daty jako jeden ze swoich argumentów.

Pierwszych osiem funkcji w tej kategorii zaczyna się od kolumny daty w bieżącym kontekście. Na przykład w przypadku używania miary w tabeli przestawnej etykiety kolumn lub wierszy mogą zawierać nazwę miesiąca lub roku. Efekt netto jest taki, że kolumna daty jest filtrowana w celu uwzględnienia tylko dat dla bieżącego kontekstu. Zaczynając od bieżącego kontekstu, te osiem funkcji oblicza poprzedni (lub następny) dzień, miesiąc, kwartał lub rok i zwraca te daty w postaci tabeli jednokolumnowej. Funkcje "poprzednie" działają wstecz od pierwszej daty w bieżącym kontekście, a funkcje "następne" poruszają się po ostatniej dacie w bieżącym kontekście.

Kolejne cztery funkcje z tej kategorii są podobne, ale zamiast obliczać poprzedni (lub następny) okres, obliczają one zestaw dat w okresie "od początku miesiąca" (lub od początku kwartału, od początku roku lub w tym samym okresie poprzedniego roku). Wszystkie te funkcje wykonują swoje obliczenia, używając ostatniej daty w bieżącym kontekście. Należy pamiętać, że funkcja SAMEPERIODLASTYEAR wymaga, aby bieżący kontekst zawierał ciągły zestaw dat. Jeśli bieżący kontekst nie jest ciągłym zestawem dat, funkcja SAMEPERIODLASTYEAR zwróci błąd.

Ostatnie cztery funkcje z tej kategorii są nieco bardziej złożone, a także nieco bardziej zaawansowane. Te funkcje umożliwiają przechodzenie z zestawu dat istniejących w bieżącym kontekście do nowego zestawu dat.

  • DATEADD (Date_Column, Number_of_Intervals, interwał)
  • DATESBETWEEN (Date_Column, Start_Date, End_Date)
  • DATA.OKRES.OKRES (Date_Column; Start_Date; Number_of_Intervals; interwał)

Funkcja DATA.MIĘDZY oblicza zestaw dat między określoną datą początkową a datą końcową. Pozostałe trzy funkcje przesuwają o pewną liczbę przedziałów czasu w stosunku do bieżącego kontekstu. Interwał może być dniem, miesiącem, kwartałem lub rokiem. Te funkcje ułatwiają przesuwanie interwału czasu dla obliczeń o dowolną z następujących opcji:

  • Wstecz dwa lata
  • Wstecz jednego miesiąca
  • Przejście do przodu o trzy kwartały
  • Wstecz 14 dni
  • Przejście do przodu o 28 dni

W każdym przypadku wystarczy określić, jaki interwał i ile interwałów ma zostać przesuniętych. Dodatni interwał spowoduje przesunięcie do przodu w czasie, podczas gdy ujemny interwał spowoduje przesunięcie w czasie do tyłu. Sam interwał jest określany przez słowo kluczowe DAY, MONTH, QUARTER lub YEAR. Te słowa kluczowe nie są ciągami, więc nie powinny być ujęte w cudzysłów.

Funkcje, które obliczają wyrażenia w danym okresie czasu

Ta kategoria funkcji oblicza wyrażenie w określonym przedziale czasu. To samo można osiągnąć za pomocą funkcji OBLICZ i innych funkcji analizy czasowej. Na przykład:

= TOTALMTD (wyrażenie, Date_Column [, SetFilter])

jest dokładnie takie samo jak:

= CALCULATE (wyrażenie, DATESMTD (Date_Column)[, UstawFiltr])

Łatwiej jest jednak korzystać z tych funkcji analizy czasowej, gdy dobrze pasują do problemu, który ma zostać rozwiązany:

  • TOTALMTD (wyrażenie, Date_Column [, ustaw_filtr])
  • TOTALQTD (wyrażenie, Date_Column [, UstawFiltr])
  • TOTALYTD (wyrażenie, Date_Column [, ustawFiltr] [,YE_Date]) *

W tej kategorii znajduje się również grupa funkcji, które obliczają salda otwarcia i zamknięcia. W przypadku tych konkretnych funkcji należy zrozumieć pewne pojęcia. Po pierwsze, co może wydawać się oczywiste, bilans otwarcia dla dowolnego okresu jest taki sam jak bilans zamknięcia poprzedniego okresu. Saldo zamknięcia obejmuje wszystkie dane do końca okresu, natomiast bilans otwarcia nie obejmuje żadnych danych z bieżącego okresu.

Te funkcje zawsze zwracają wartość wyrażenia obliczanego dla określonego punktu w czasie. Ważny dla nas punkt jest zawsze ostatnią możliwą datą w okresie kalendarzowym. Bilans otwarcia jest obliczany na podstawie ostatniego dnia poprzedniego okresu, natomiast bilans zamknięcia jest obliczany na podstawie ostatniego dnia w bieżącym okresie. Bieżący okres jest zawsze ustalany przez ostatnią datę w kontekście bieżącej daty.

Dodatkowe zasoby

Artykuły: opis i tworzenie tabel dat w dodatku Power Pivot w programie Excel

Informacje: Funkcje języka DAX — odwołanie do Office.com

Przykłady: Modelowanie i analiza danych zysków i strat za pomocą dodatku Microsoft PowerPivot w programie Excel