Poznawanie nowych narzędzi powoduje, że często zastanawiam się, czy daną funkcjonalność da się utworzyć w programie Excel. Tym razem szukałem sposobu na ustawienie filtra typu Ten miesiąc lub Ten rok (charakterystycznego dla tabel przestawnych) do zdefiniowania filtru globalnego dla raportu.

[grwebform url="https://app.getresponse.com/view_webform_v2.js?u=N3vX&webforms_id=9648602" css="on" center="off" center_margin="0"/]

Gdzie znaleźć filtr czasowy tabeli przestawnej w stylu Ten miesiąc, Ten rok itp.?

Kiedy wstawimy tabelę przestawną i wrzucimy pole rozpoznawane przez Excel jako data do tabeli przestawnej, otrzymamy dodatkowe opcje filtra tabeli przestawnej. Polecenie to działa zarówno dla zwykłej tabeli przestawnej, jak i tej utworzonej na modelu danych, edytowanym przez Power Pivot.

Gdzie można znaleźć globalny fragmentator czasowy (ang. slicer) tego typu?

Podobny filtr czasowy możemy znaleźć w aplikacji analitycznej Power BI Desktop, gdzie w ustawieniach fragmentatora czasowego znajdziemy filtr Względnie, który pozwala wybrać aktualny (oraz zeszły lub przyszły) okres, np. aktualny rok (tłumaczony w momencie pisania artykułu ze słowa „This” Year na „To” Rok ?).

Jak utworzyć w Excelu globalny filtr czasowy?

Rozwiązanie, do którego zmierzamy, będzie działać tylko dla standardowego kalendarza gregoriańskiego (czyli takiego, który wisi na ścianie; nie znajdzie tu zastosowanie kalendarz niestandardowy) w Excelu >= 2013 (2013, 2016, 365 i kolejnych). Funkcjonalność, którą opisuję, jest powiązana z filtrowaniem przez tabele przestawne w Excelu fragmentatora, którą poprzednio opisywałem.

Co ciekawe, technika ta nie działa dla daty w przypadku standardowego fragmentatora (slicera). Po wstawieniu tabeli przestawnej, która ma filtrować fragmentator, który z kolei będzie filtrował kolejne tabele przestawne, mechanizm filtrowania nie działa prawidłowo.

Inaczej się ma sytuacja, gdy wstawimy fragmentator w postaci osi czasu (ang. timeline). Musimy tylko pamiętać, aby w połączeniach raportu ustawić filtrowanie wielu tabel.

Wówczas niespodziewanie mechanizm zaczyna działać – tabela przestawna staje się filtrem tabeli „slicera”, który filtruje raport. Poniżej przykład zastosowania fragmentatora dla tabeli przestawnej pokazującej wynik za dany rok w odniesieniu do planu z wykorzystaniem licznika Excel.

Kiedy przyda się filtr czasowy Od początku roku (YTD)?

Jedną z opcji filtru czasowego jest Od początku roku. Opcja ta pokazuje wyniki w odniesieniu do aktualnej daty systemowej. Kiedy wybrać wynik Ten rok a kiedy Od początku roku? Jeśli mamy w bazie danych lub tabeli źródłowej daty przyszłe (np. prognozy lub plany), wtedy opcja ta może okazać się zbawienna.

Ograniczenia tego rozwiązania

Niestety filtr nie odświeża się sam przy aktualizacji raportu i trzeba go przeładować – zdjąć i założyć ponownie. Dużo lepiej działałaby zatem kalkulacja w stylu Year-To-Date lub Month-To-Date, która dostępna jest w modelu danych Power Pivot i Power BI: TOTALYTD i TOTALMTD.