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.
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.
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.
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.
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.
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.
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.
Plik z omawianym przykładem możesz pobrać poniżej.
