Excel DAX - analiza czasowa
Język DAX ma ważną i wszechstronną funkcję, zwaną dalej Time Intelligence. Analiza czasowa umożliwia pisanie formuł języka DAX, które odwołują się do okresów do użycia w tabelach przestawnych.
Język DAX ma 35 funkcji analizy czasowej przeznaczonych specjalnie do agregowania i porównywania danych w czasie. Jednak te funkcje języka DAX mają pewne ograniczenia dotyczące danych, które należy zrozumieć i zachować ostrożność, aby uniknąć błędów.
Dlaczego analiza czasowa sprawia, że język DAX jest potężny?
Funkcje analizy czasowej działają z danymi, które stale się zmieniają, w zależności od kontekstu wybranego w tabelach przestawnych i wizualizacjach programu Power View. Jak wiesz, większość analizy danych obejmuje podsumowywanie danych w różnych okresach, porównywanie wartości danych w różnych okresach, zrozumienie trendów i podejmowanie decyzji na podstawie przyszłych prognoz.
Na przykład możesz chcieć zsumować kwoty sprzedaży z ostatniego miesiąca według produktu i porównać te sumy z sumami z innych miesięcy w roku obrotowym. Oznacza to, że musisz używać dat jako sposobu na grupowanie i agregowanie transakcji sprzedaży dla określonego okresu w czasie.
W tym miejscu możesz zaobserwować moc języka DAX. Funkcji analizy czasowej języka DAX można używać do definiowania pól obliczeniowych, które ułatwiają analizowanie danych w czasie bez konieczności zmiany wybranych dat w tabelach przestawnych. Ułatwia to pracę. Ponadto możesz tworzyć tabele przestawne, które nie byłyby możliwe w żaden inny sposób.
Wymagania dotyczące funkcji analizy czasowej języka DAX
Funkcje analizy czasowej języka DAX mają określone wymagania. Jeśli te wymagania nie zostaną spełnione, mogą wystąpić błędy lub mogą one nie działać poprawnie. Dlatego też do tych wymagań można odnosić się jako do reguł lub ograniczeń. Poniżej przedstawiono niektóre wymagania / reguły / ograniczenia dotyczące funkcji analizy czasowej języka DAX -
Musisz mieć tabelę dat w swoim modelu danych.
Tabela dat musi zawierać kolumnę uznawaną za kolumnę Data według języka DAX. Możesz nazwać kolumnę tak, jak chcesz, ale powinna ona spełniać następujące warunki: o Kolumna dat powinna zawierać ciągły zestaw dat obejmujący każdy dzień okresu, w którym analizujesz dane.
Każda data musi występować tylko raz w kolumnie daty.
Nie możesz pominąć żadnych dat (np. Nie możesz pominąć dat weekendowych).
Funkcje analizy czasowej języka DAX działają tylko w standardowym kalendarzu i przyjmują początek roku jako 1 stycznia, a koniec roku jako 31 grudnia, z miesiącami w roku i dniami każdego miesiąca jako rokiem kalendarzowym.
Możesz jednak dostosować standardowy kalendarz na różne lata obrotowe. Dobrą praktyką jest zweryfikowanie powyższych wymagań przed użyciem funkcji inteligencji.
Aby uzyskać więcej informacji na temat tabel dat i ich użycia w formułach języka DAX, zapoznaj się z samouczkiem = Modelowanie danych za pomocą języka DAX w tej bibliotece samouczków.
Funkcje analizy czasowej języka DAX - kategorie
Funkcje analizy czasowej języka DAX można podzielić na następujące kategorie:
- Funkcje języka DAX, które zwracają pojedynczą datę.
- Funkcje języka DAX, które zwracają tabelę dat.
- Funkcje języka DAX, które oceniają wyrażenia w okresie.
Funkcje języka DAX, które zwracają pojedynczą datę
Funkcje języka DAX w tej kategorii zwracają pojedynczą datę.
W tej kategorii znajduje się 10 funkcji języka DAX -
| Sr.No. | Funkcja języka DAX i wartość zwracana |
|---|---|
| 1 | FIRSTDATE (Date_Column) Zwraca pierwszą datę w kolumnie Date_Column w bieżącym kontekście. |
| 2 | LASTDATE (Date_Column) Zwraca ostatnią datę w kolumnie Date_Column w bieżącym kontekście. |
| 3 | FIRSTNONBLANK (Date_Column, Expression) Zwraca pierwszą datę, w której wyrażenie nie jest puste. |
| 4 | LASTNONBLANK (Date_Column, Expression) Zwraca ostatnią datę, w której wyrażenie nie jest puste. |
| 5 | STARTOFMONTH (Date_Column) Zwraca pierwszą datę miesiąca w bieżącym kontekście. |
| 6 | ENDOFMONTH (Date_Column) Zwraca ostatnią datę miesiąca w bieżącym kontekście. |
| 7 | STARTOFQUARTER (Date_Column) Zwraca pierwszą datę kwartału w bieżącym kontekście. |
| 8 | ENDOFQUARTER (Date_Column) Zwraca ostatnią datę kwartału w bieżącym kontekście. |
| 9 | STARTOFYEAR (Date_Column, [YE_Date]) Zwraca pierwszą datę roku w bieżącym kontekście. |
| 10 | ENDOFYEAR (Date_Column, [YE_Date]) Zwraca ostatnią datę roku w bieżącym kontekście. |
Funkcje języka DAX, które zwracają tabelę dat
Funkcje języka DAX w tej kategorii zwracają tabelę dat. Te funkcje będą najczęściej używane jako argument SetFilter funkcji języka DAX - CALCULATE.
W tej kategorii jest 16 funkcji języka DAX. Osiem (8) z tych funkcji języka DAX to funkcje „poprzednia” i „następna”.
Funkcje „poprzedni” i „następny” rozpoczynają się od kolumny daty w bieżącym kontekście i obliczają poprzedni lub następny dzień, miesiąc, kwartał lub rok.
Funkcje „poprzednie” działają wstecz od pierwszej daty w bieżącym kontekście, a funkcje „następne” przesuwają się do przodu od ostatniej daty w bieżącym kontekście.
Funkcje „previous” i „next” zwracają wynikowe daty w postaci pojedynczej kolumny tabeli.
| Sr.No. | Funkcja języka DAX i wartość zwracana |
|---|---|
| 1 | PREVIOUSDAY (Date_Column) Zwraca tabelę zawierającą kolumnę wszystkich dat reprezentujących dzień poprzedzający pierwszą datę w kolumnie Date_Column w bieżącym kontekście. |
| 2 | NEXTDAY (Date_Column) Zwraca tabelę zawierającą kolumnę wszystkich dat od następnego dnia na podstawie pierwszej daty określonej w kolumnie Date_Column w bieżącym kontekście. |
| 3 | PREVIOUSMONTH (Date_Column) Zwraca tabelę zawierającą kolumnę wszystkich dat z poprzedniego miesiąca na podstawie pierwszej daty w kolumnie Date_Column w bieżącym kontekście. |
| 4 | NEXTMONTH (Date_Column) Zwraca tabelę zawierającą kolumnę wszystkich dat z następnego miesiąca na podstawie pierwszej daty w kolumnie Date_Column w bieżącym kontekście. |
| 5 | PREVIOUSQUARTER (Date_Column) Zwraca tabelę zawierającą kolumnę wszystkich dat z poprzedniego kwartału na podstawie pierwszej daty w kolumnie Date_Column w bieżącym kontekście. |
| 6 | NEXTQUARTER (Date_Column) Zwraca tabelę zawierającą kolumnę wszystkich dat w następnym kwartale na podstawie pierwszej daty określonej w kolumnie Date_Column w bieżącym kontekście. |
| 7 | PREVIOUSYEAR (Date_Column, [YE_Date]) Zwraca tabelę zawierającą kolumnę wszystkich dat z poprzedniego roku, biorąc pod uwagę ostatnią datę w kolumnie Date_Column w bieżącym kontekście. |
| 8 | NEXTYEAR (Date_Column, [YE_Date]) Zwraca tabelę zawierającą kolumnę wszystkich dat w następnym roku na podstawie pierwszej daty w kolumnie Date_Column w bieżącym kontekście. |
Cztery (4) funkcje języka DAX obliczają zestaw dat w okresie. Funkcje te wykonują obliczenia przy użyciu ostatniej daty w bieżącym kontekście.
| Sr.No. | Funkcja języka DAX i wartość zwracana |
|---|---|
| 1 | DATESMTD (Date_Column) Zwraca tabelę zawierającą kolumnę z datami od początku miesiąca w bieżącym kontekście. |
| 2 | DATESQTD (Date_Column) Zwraca tabelę zawierającą kolumnę z datami od początku kwartału w bieżącym kontekście. |
| 3 | DATESYTD (Date_Column, [YE_Date]) Zwraca tabelę zawierającą kolumnę z datami od początku roku w bieżącym kontekście. |
| 4 | SAMEPERIODLASTYEAR (Date_Column) Zwraca tabelę zawierającą kolumnę dat przesuniętą o rok wstecz w stosunku do dat w określonej kolumnie Date_Column w bieżącym kontekście. Note- SAMEPERIODLASTYEAR wymaga, aby bieżący kontekst zawierał ciągły zestaw dat. Jeśli bieżący kontekst nie jest ciągłym zestawem dat, SAMEPERIODLASTYEAR zwróci błąd. |
Cztery (4) funkcje języka DAX służą do przechodzenia z zestawu dat w bieżącym kontekście do nowego zestawu dat.
Te funkcje języka DAX są bardziej zaawansowane niż poprzednie.
Funkcje języka DAX - DATEADD, DATESINPERIOD i PARALLELPERIOD przesuwają pewną liczbę przedziałów czasu z bieżącego kontekstu. Przedział może być dniem, miesiącem, kwartałem lub rokiem, reprezentowany przez słowa kluczowe - odpowiednio - DZIEŃ, MIESIĄC, KWARTER i ROK.
Na przykład:
Przesuń do tyłu o 2 dni.
Przejdź do przodu o 5 miesięcy.
Przejdź do przodu o jeden miesiąc od dzisiaj.
Wróć do tego samego kwartału ubiegłego roku.
Funkcja języka DAX - DATESBETWEEN oblicza zestaw dat między określoną datą początkową a datą końcową.
Jeśli argument funkcji - liczba przedziałów (liczba całkowita) jest dodatnia, przesunięcie jest do przodu, a jeśli jest ujemne, to przesunięcie jest do tyłu.
| Sr.No. | Funkcja języka DAX i wartość zwracana |
|---|---|
| 1 | DATEADD (Date_Column, Number_of_Intervals, Interval) Zwraca tabelę zawierającą kolumnę dat, przesuniętą do przodu lub do tyłu w czasie o określoną liczbę interwałów od dat w bieżącym kontekście. |
| 2 | DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval) Zwraca tabelę zawierającą kolumnę dat, która zaczyna się od daty_początkowej i trwa przez określoną liczbę_zakresów. |
| 3 | PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval) Zwraca tabelę zawierającą kolumnę dat, która reprezentuje okres równoległy do dat w określonej kolumnie Date_Column w bieżącym kontekście, z datami przesuniętymi o liczbę interwałów do przodu lub do tyłu w czasie. |
| 4 | DATESBETWEEN (Date_Column, Start_Date, End_Date) Zwraca tabelę zawierającą kolumnę dat, która zaczyna się od daty początkowej i trwa aż do daty końcowej. |
Funkcje języka DAX, które oceniają wyrażenia w okresie
Funkcje języka DAX w tej kategorii oceniają wyrażenie w określonym przedziale czasu.
W tej kategorii jest dziewięć (9) funkcji języka DAX -
Trzy (3) funkcje języka DAX w tej kategorii mogą służyć do oceny dowolnego wyrażenia w określonym przedziale czasu.
| Sr.No. | Funkcja języka DAX i wartość zwracana |
|---|---|
| 1 | TOTALMTD (Expression, Date_Column, [SetFilter]) Oblicza wartość wyrażenia dla dat od początku miesiąca w bieżącym kontekście. |
| 2 | TOTALQTD (Expression, Date_Column, [SetFilter]) Oblicza wartość wyrażenia dla dat od początku kwartału w bieżącym kontekście. |
| 3 | TOTALYTD (Expression, Date_Column, [SetFilter], [YE_Date]) Oblicza wartość wyrażenia dla dat od początku roku w bieżącym kontekście |
Sześciu (6) funkcji języka DAX w tej kategorii można użyć do obliczenia salda otwarcia i zamknięcia.
Saldo otwarcia dla dowolnego okresu jest takie samo, jak saldo zamknięcia z poprzedniego okresu.
Saldo zamknięcia obejmuje wszystkie dane do końca okresu, natomiast saldo otwarcia nie obejmuje żadnych danych z bieżącego okresu.
Te funkcje języka DAX zawsze zwracają wartość wyrażenia obliczonego dla określonego punktu w czasie.
Punkt w czasie, na którym nam zależy, to zawsze ostatnia możliwa wartość daty w okresie kalendarzowym.
Bilans otwarcia oparty jest na ostatniej dacie poprzedniego okresu, a saldo zamknięcia na ostatniej dacie w bieżącym okresie.
Bieżący okres jest zawsze określany na podstawie ostatniej daty w kontekście bieżącej daty.
| Sr.No. | Funkcja języka DAX i wartość zwracana |
|---|---|
| 1 | OPENINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Oblicza wyrażenie w pierwszym dniu miesiąca w bieżącym kontekście. |
| 2 | CLOSINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Oblicza wyrażenie w ostatnim dniu miesiąca w bieżącym kontekście. |
| 3 | OPENINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Ocenia wyrażenie w pierwszym dniu kwartału w bieżącym kontekście. |
| 4 | CLOSINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Ocenia wyrażenie w ostatnim dniu kwartału w bieżącym kontekście. |
| 5 | OPENINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Ocenia wyrażenie w pierwszym dniu roku w bieżącym kontekście. |
| 6 | CLOSINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Oblicza wyrażenie w ostatnim dniu roku w bieżącym kontekście. |