Czas na najczęstsze wzory wykorzystania formuły CALCULATE dla analiz czasowych: od początku roku (YTD, Year-To-Date), 3-miesięczna średnia ruchoma, dynamika rok do roku.

CALCULATE i tabela Kalendarz

Wtajemniczeni użytkownicy Power Pivota i modelu danych Power BI wiedzą, że modele, w których występuje tabela dat (kalendarz), można wzbogacić o wiele miar, które dają możliwość analizy danych z perspektywy czasu w różnych ujęciach. Wykorzystajmy więc taki właśnie model, żeby przekonać się, jak działa CALCULATE w połączeniu z analizą czasową.

Oprócz kalendarza, w modelu danych mamy tabelę z raportem sprzedaży. Stwórzmy więc tabelę przestawną, która pokaże nam sumę sprzedaży po miesiącach z roku 2010. W tym celu dodajmy do obszaru filtrów kolumnę „Rok” z tabeli „Kalendarz”, do obszaru wierszy kolumnę „miesiąc_rok” z tej samej tabeli, a do obszaru wartości kolumnę „Sprzedaż” z tabeli „Sprzedaż”. Ostatnia operacja stworzy niejawne pole obliczeniowe sumujące wartość sprzedaży. Oto wynik:

Obliczanie sprzedaży od początku roku z użyciem CALCULATE i DATESYTD

Aby obejrzeć te same dane w ujęciu kumulatywnym, będziemy posiłkować się funkcją DATESYTD, która zastąpi kontekst filtra tabeli przestawnej i użyje danych z wierszy do zbudowania nowego kontekstu – YTD (Year To Date, czyli od początku roku do dnia dzisiejszego). Dla stycznia będą to dane styczniowe, ale dla lutego – suma danych stycznia i lutego, dla marca – suma pierwszego kwartału i tak dalej.

Miara będzie więc zbudowana w następujący sposób – na miarę „Suma Sprzedaż” nałożymy funkcję CALCULATE, a jako filtra użyjemy funkcji DATESYTD:

=CALCULATE([Suma Sprzedaż]; DATESYTD(Kalendarz[Data]))

Efekt tej operacji będzie taki:

Widzimy, że pomimo iż wiersze wskazują na konkretny miesiąc, nasza miara w każdym wierszu pokazuje dane od początku roku.

Aby uatrakcyjnić tabelę, można dodać do niej proste formatowanie warunkowe, które zwizualizuje rosnącą wartość sprzedaży. Zaznaczmy wartości w kolumnie „Sprzedaż YTD” i przejdźmy do menu „Formatowanie warunkowe” na wstążce „Narzędzia główne”. Z dostępnych opcji wybierzmy Paski danych o pełnym wypełnieniu w dowolnym kolorze:

Obliczanie trzymiesięcznej średniej ruchomej z pomocą DATESINPERIOD i LASTDATE

Funkcji CALCULATE możemy również użyć do wyliczenia trzymiesięcznej średniej ruchomej ze sprzedaży. Jak to zrobić? W tym celu najpierw napiszemy formułę, która wyliczy trzymiesięczną sumę ruchomą, a następnie kolejną formułę, która podzieli tę sumę na trzy, czyli policzy trzymiesięczną średnią.

Początek naszej miary będzie identyczny – pierwszym argumentem dla CALCULATE będzie „Suma Sprzedaż”. Dalej będzie nieco trudniej, ale do opanowania. Użyjemy bowiem funkcji DATESINPERIOD. Funkcja ta robi dokładnie to, co wskazuje jej nazwa – mianowicie wybiera daty z zadanego ruchomego okresu. Ponieważ operacja, którą wykonuje, jest odrobinę piętrowa, funkcja ta potrzebuje aż czterech argumentów. Musimy wskazać, gdzie znajdują się daty, która data jest datą startową, ile interwałów ma być wziętych pod uwagę i co będzie tym interwałem (dni, tygodnie, miesiące czy lata). W naszym przypadku posłużymy się małym fortelem, ponieważ zamiast daty startu podamy końcową datę z naszego kalendarza, a ilość interwałów napiszemy z minusem – tym sposobem funkcja będzie liczyć do tyłu. Aby wskazać końcową datę kalendarza, również użyjemy funkcji, a będzie to LASTDATE.

Na podstawie powyższego akapitu można już domyślić się, że nasza miara będzie miała taką oto postać:

=CALCULATE([Suma Sprzedaż]; DATESINPERIOD(Kalendarz[Data]; LASTDATE(Kalendarz[Data]); -3; month))

Po zapisaniu jej w modelu, tabela przestawna zyskała kolejną kolumnę:

Ponieważ jednak poszukujemy trzymiesięcznej średniej, a nie sumy, czym prędzej usuwamy tę miarę z tabeli i dodajemy nową miarę:

= [trzymiesięczna suma ruchoma] / 3

Widzimy teraz właściwe dane:

Obliczanie dynamiki sprzedaży rok do roku z formułą DAX SAMEPERIODLASTYEAR

Trzecim przykładem zastosowania funkcji CALCULATE w połączeniu z funkcjami analizy czasowej niech będzie miara pokazująca wzrost względem tego samego okresu roku ubiegłego.

Aby policzyć taki wzrost wystarczy podzielić wartość sprzedaży przez wartość sprzedaży sprzed roku, a dla jasności przekazu odjąć od całości 1 i pokazać w ujęciu procentowym.

Argumentem filtra dla funkcji CALCULATE będzie w tym przypadku kolejna funkcja analizy czasowej, a mianowicie SAMEPERIODLASTYEAR, której wystarczy wskazać daty z tabeli „Kalendarz”. Funkcja CALCULATE w tej mierza wyglądać więc będzie następująco:

=CALCULATE([Suma Sprzedaż]; SAMEPERIODLASTYEAR(Kalendarz[Data]))

A miara w całości przyjmie taką postać:

=[Suma Sprzedaż]/CALCULATE([Suma Sprzedaż]; SAMEPERIODLASTYEAR(Kalendarz[Data]))-1

Po dodaniu odpowiedniego formatowania (procentowe), tabela przestawna będzie wyglądać tak:

Jak widać na powyższych przykładach, za pomocą funkcji CALCULATE i funkcji analizy czasowej można w prosty sposób wprowadzić do swoich raportów ciekawe obliczenia i tym samym pozwolić na obserwację danych w wielu ujęciach.