Stań ponad liczbami, odejdź od roli szeregowego pracownika i stań się partnerem biznesowym.

Wartość dla poprzedniego okresu w Power Pivot

Tworząc raporty oraz wskaźniki często musimy wracać do wcześniejszych dat, nteresuje nas jak wyglądają dane względem poprzednich wartości. Pracując bezpośrednio na Excelu jest to bardzo proste. Wystarczy użyć odpowiednich formuł lub odwołań do wcześniejszych komórek i już mamy dostęp do wcześniejszych danych. Trudniejsze jest to w przypadku tablic i stosowania formuł tablicowych. Na podobnej zasadzie działa też formuły Power Pivot. aby określić wartość dla poprzedniego okresu w Power Pivot musimy dobrze zrozumieć obliczenia w Power Pivot, a przede wszystkim formuła CALCULATE.

Czym jest wartość dla poprzedniego okresu w Power Pivot

Aby rozpocząć jakiekolwiek obliczenia w pierwszym kroku musimy zdefiniować czym dla nas jest wartość poprzedniego okresu. Choć wydawałoby się to oczywiste warto chwilę poświęcić na to, aby przemyśleć to zagadnienie. Poprzednia wartość może występować w kilku wariantach, może nas interesować suma wartości z poprzedniego okresu, na przykład jaka jest wartość sprzedaży za poprzedni miesiąc. W innym przypadku może nas interesować jaka jest ostatnia wartość dla danych parametrów. W tym przypadku wartość nie musiała wystąpić w poprzednim okresie, ale chcemy znać ostatnią wartość jaka w ogóle występowała. Dziś się zajmiemy pierwszym przypadkiem.

Przykładowe dane oraz model danych.

Nasz przykładowy model danych składa się z dwóch tabel. W jednej mamy informacje o notowaniach giełdowych indeksów WIG20 i WIG30. Druga tabela zawiera wszystkie daty od 1 stycznia 2020 do 31 grudnia 2020. Relacja między tabelami tworzymy na kolumnach z datami. Tabela z datami służy nam za tak zwany Kalendarz dat. Jest to specjalnie oznaczona tabela w modelu danych, dzięki której możemy wykorzystywać specjalne formuły działające na datach oraz co też istotne, mamy wszystkie dni, miesiące itp. w naszych tabelach przestawnych. Tabelę oznaczamy jako tabelę dat po zaznaczeniu jej i przejściu na kartę Projekt. Tam mamy dedykowany do tego przycisk. Tylko jedną tabelę możemy oznaczyć jako tabelę dat.

Model danych w Power Pivot

Prosta wartość dla poprzedniego okresu

Załóżmy, że chcemy wyliczyć sumę obrotu dla obecnego i poprzedniego miesiąca, a następnie je porównać. W tym przykładzie wykorzystamy miary tworzone w obszarze obliczeń w Power Pivocie. Takie miary służą do obliczeń na całych kolumnach lub ich odpowiednio zafiltrowanej części. Nie możemy ich stosować do obliczeń, które muszą być wykonywane na pojedynczych rekordach.

Suma obrotu – najprostsza miara

Ta miara jest banalnie prosta. Podajemy nazwę miary, dwukropek i wzór naszej miary. Wystarczy że użyjemy tutaj funkcji SUM oraz podać kolumnę, z której wartości mają być sumowane.

Suma wolumenu w POwer Pivot

Wartość dla poprzedniego okresu w Power Pivot

Wyliczenie sumy dla poprzedniego miesiąca byłoby o wiele trudniejsze, jeśli nie mielibyśmy dedykowanych funkcji, które świetnie działają na obliczeniach dotyczących czasu. Musimy tu użyć funkcji CALCULATE. Funkcji, która jest bazą do wszelkich obliczeń w Power Pivot. Składa się ona z dwóch elementów, jej pierwszy parametr to określenie co chcemy obliczyć. Może to być suma, średnia, ilości a także wiele bardziej skomplikowanych miar. Druga część funkcji to zestaw filtrów, które określają jakie dane mają być podstawą obliczeń. Ze względu na to, że parametrami są kolumny lub całe tabele to weryfikacja poprawności obliczeń jest trudniejsza niż w Excelu.

Chcąc wyliczyć wartość dla poprzedniego okresu w formule CALCULATE musimy użyć podobnie jak wcześniej formuły Sum z kolumny VOL. Następnie jako filtr użyjemy funkcji PREVIOUSMONTH, a jej parametrem musi być kolumna z datami. Ta funkcja zastosowana jako filtr automatycznie ograniczy zakres danych, które będą brane do sumowania.

Wartość dla poprzedniego okresu w Power Pivot

Po wprowadzeniu formuły łatwo dostrzec, główny problem w zrozumieniu obliczeń na tabelach i kolumnach. Zamiast wartości widzimy napis (puste), co oznacza, że na widocznych danych wynik nie może być określony. Przypominam, że te miary działają na wartościach jakie aktualnie mamy w tabeli. Jeśli je przefiltrujemy to zmienią się też wyniki. Aby zobaczyć czy nasze obliczenia są poprawne należy skorzystać z tabeli przestawnej, to najlepszy sposób.

Wartość dla poprzedniego okresu w Power Pivot - prezentacja wyników

Wykorzystanie wartości dla poprzedniego okresu w Power Pivot

Wartości z poprzednich okresów najczęściej wykorzystujemy do porównania ich z obecnymi wartościami. Policzmy sobie różnicę wartościową oraz dynamikę zmian.

Różnica wartościowa

Wystarczy po prostu dodać nową miarę, w której wyliczymy różnicę pomiędzy wcześniejszymi miarami sumującymi.

Zmiana wartościowa obrotu

Dynamika zmian

Tu również wzór formuły może być prosty. Jeśli mamy już miarę liczącą różnicę wartościową to wystarczy ją podzielić przez obecną sumę, dzięki temu otrzymamy dynamikę zmian. Ze względu na użyte dzielenie wartości warto też dołożyć funkcję IFERROR, aby wyeliminować błędy przy dzieleniu przez zero. Ponadto warto ustawić typ danych na procentowy.

Zmiana procentowa - dynamika

Podsumowanie

Jak widać zastosowanie modelu danych zawierającego tabelę dat pozwala na bardzo szybkie i proste obliczenia, jeśli chcemy porównywać się do wcześniejszych okresów. Oczywiście można użyć formuł, które w inny sposób określą odpowiedni zakres czasowy, ale zawsze będzie to rozwiązanie bardziej skomplikowane. W tym przykładzie skupiliśmy się na poprzednim miesiącu, ale również dostępne są formuły PREVIOUSDAY, PREVIOUSYEAR oraz PREVIOUSQUARTER, które łatwo wykorzystujemy do innych obliczeń. Ponadto warto pamiętać o dobrych praktykach prezentacji danych, co w tym przykładzie oznacza niepokazywanie maja 2021, który nie zawiera poprawnych kompletnych wartości.

Wartość dla poprzedniego okresu w Power Pivot - prezentacja wyników i miar

Plik z omawianym przykładem możesz pobrać poniżej.

Zapisz się
Powiadom o
guest
0 komentarzy
Informacje zwrotne w treści
Pokaż wszystkie komentarze

Pobierz za darmo ebooka z 8 rzeczami które na zawsze zmienią Twoją pracę z Excelem

Alademia Analitykow newsletter
0
Spodobało Ci się? Zostaw komentarzx