Pobierz wartości LuckyTemplates z poprzedniego tygodnia przy użyciu języka DAX i dodatku Power Query

Pobieranie wartości z poprzedniego okresu w usłudze LuckyTemplates jest częstym zadaniem, ale pobieranie wartości z poprzedniego tygodnia wymaga szczególnej uwagi. W tym samouczku omówię dwie proste i wszechstronne metody uzyskiwania wartości usługi LuckyTemplates z poprzedniego tygodnia, które mogą stanowić przydatne dodatki do Twojego zestawu narzędzi — jedną przy użyciu języka DAX, a drugą przy użyciu dodatku Power Query. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Ten samouczek powstał na podstawie pytania, które otrzymałem od członka LuckyTemplates. Członek zapytał, jaki był najłatwiejszy sposób określenia liczby z poprzedniego tygodnia. To częste pytanie, z którym się spotykam. Wydaje się to dość proste, ale im bardziej się w to zagłębiasz, tym bardziej zdajesz sobie sprawę, że jest całkiem sporo do przetworzenia.

Spis treści

• Wartości z poprzedniego tygodnia w usłudze LuckyTemplates: problem
• Uzyskiwanie usługi LuckyTemplates w poprzednim tygodniu przy użyciu języka DAX
• Wniosek

Wartości z poprzedniego tygodnia w usłudze LuckyTemplates: problem

Jeśli spojrzymy na numer tygodnia, mamy 52 tygodnie; pomnóż to przez siedem dni (tydzień) i wychodzi 364. Tak więc w każdym roku jest dodatkowy dzień, aw latach przestępnych są dwa dodatkowe dni. Te jeden lub dwa dodatkowe dni powodują wiele problemów.

Utwórzmy miarę z naszej tabeli Daty. Nazwiemy go MAX Tydzień # (liczba). W tej analizie używamy rozszerzonej tabeli dat LuckyTemplates; to ma ISO Weeknumber , co jest bardzo pomocne. Teraz weźmy MAX numeru tygodnia ISO.

Teraz weźmy stół i upuśćmy Rok , a także nasz MAX Tydzień # . Ten zestaw danych zawiera dane z 10 lat i widzimy, że pierwsze dwa lata mają 52 tygodnie. Następnie mamy dwa lata z 53 tygodniami, trzy lata z 52 tygodniami, a potem jeszcze dwa lata z 53 i jeden z 52.

Nie ma tu wyraźnego wzorca. Nie jest łatwo cofnąć się i przewidzieć, że jednego roku będziesz mieć 52 tygodnie, a drugiego 53.

Następną rzeczą, o której możesz pomyśleć, jeśli znasz funkcje analizy czasowej języka DAX, jest użycie funkcji DATEADD , która przesuwa zakres dat wstecz o dany okres. Robi to naprawdę dobrze dla dnia, miesiąca, kwartału i roku, ale nie obejmuje tygodnia, więc musimy znaleźć inny sposób na zrobienie tego.

W tabeli LuckyTemplates Extended Dates, której używamy, Melissa (jeden z ekspertów LuckyTemplates) umieściła funkcję o nazwie OFFSETS . Zagłębmy się w tę funkcję, ponieważ jest ona absolutnie kluczowa w rozwiązaniu tego problemu z perspektywy języka DAX.

Patrząc na bieżący okres (na przykład 20 stycznia, który jest okresem tworzenia tego samouczka), widzimy, że przesunięcie jest równe zeru . Sposób działania przesunięć polega na tym, że dla bieżącego okresu przesunięcie wynosi zero.

Tydzień poprzedzający bieżący tydzień to wynik ujemny, dwa tygodnie przed bieżącym tygodniem to wynik ujemny dwa i tak dalej. A potem idzie naprzód od liczb dodatnich; następny tydzień dostaje jeden, dwa tygodnie dostaje dwa itd.

Za każdym razem, gdy otwierasz raport względem dzisiejszej daty, oblicza on przesunięcia. Jest to niewiarygodnie potężne, a teraz pokażę ci, jak łatwo rozwiązać niektóre trudne problemy, takie jak ten numer LuckyTemplates z poprzedniego tygodnia, gdy używasz przesunięć.

Uzyskiwanie usługi LuckyTemplates w poprzednim tygodniu przy użyciu języka DAX

Utwórzmy nową miarę i nazwijmy ją Poprzedni tydzień # . Zaczniemy od zmiennej ( SelWkOffset ), gdzie po prostu zrobimy SELECTEDVALUE z WeekOffset . Więc w bieżącym wierszu narysuje to przesunięcie tygodnia i wstawi je do naszej zmiennej.

Następną zmienną będzie Result . To jest ostateczny wynik, którego chcemy i użyjemy CALCULATE , ponieważ będziemy zmieniać kontekst.

Następnie chcemy MAX ISO Weeknumber i to będzie podlegać tym warunkom filtra. Usuniemy więc wszystkie filtry z tabeli Daty i użyjemy tutaj WSZYSTKICH . Gdybyśmy mieli fragmentator dat, moglibyśmy chcieć użyć ALLSELECTED , ale na razie nie mamy, więc użyjemy ALL, aby usunąć filtr z tej tabeli dat.

A potem powiemy, że WeekOffset będzie równy naszemu SelWkOffset minus jeden . W tym miejscu OFFSETY stają się tak potężne. Nie możesz użyć numeru tygodnia minus jeden, ponieważ resetuje się on co roku. Ale ponieważ WSZYSTKIE PRZESUNIĘCIA są sekwencyjne, możesz traktować to tak, jakbyś traktował znajdowanie poprzedniego roku i po prostu odejmowanie jednego za każdy poprzedni rok. A więc to przesunięcie działa dokładnie tak samo jak kolejna liczba.

Możemy teraz wziąć i zamknąć ten warunek filtra, zamknąć CALCULATE i po prostu ZWRÓCIĆ nasz Result .

Przyjrzyjmy się teraz, jak działa ten środek. W poniższej tabeli widzimy, że robi dokładnie to, na co liczyliśmy. W tygodniu 52 dla okresu pierwszego w następnym roku poprzedni tydzień wynosi 52. Następnie cofa się o jeden okres w każdym okresie i tak dalej.

A teraz, co robisz w przypadkach, gdy nie masz offsetu?

Załóżmy, że pracujesz z korporacyjną tabelą dat, która pochodzi z Twojej hurtowni danych i nie zawiera tych przesunięć. Jest pewna technika, którą chcę ci pokazać w dodatku Power Query. Po raz pierwszy zobaczyłem to we wpisie na blogu Imke Feldmann.

Takie podejście sprawdza się naprawdę dobrze w każdej sytuacji, w której można uporządkować tabelę w porządku rosnącym i nie musi ona mieć przesunięcia. Nie musi mieć nawet pola daty. Może to być zupełnie inny rodzaj stołu. Tak długo, jak można go sortować w porządku rosnącym, możesz użyć tej techniki.

Odwołajmy się więc do tej tabeli Dates Raw i nazwijmy ją Dates . Następnie upewniamy się, że posortujemy to w porządku rosnącym .

Następnie chcemy pogrupować według tygodnia . Powodem tego jest to, że zamierzamy dodać dwie kolumny indeksu. Kiedy dodajemy te kolumny indeksu, chcemy, aby były one na poziomie szczegółowości tygodniowej , a nie dziennej.

Następnie chcemy dodać te kolumny indeksu. Ważną rzeczą jest to, że pierwszy indeks, który dodamy, będzie indeksem liczonym od 0. A potem dodamy drugi indeks, który jest indeksem opartym na 1.

Następnie weźmiemy tę kolumnę i połączymy tabelę ze sobą. Zamierzamy scalić go na podstawie dwóch różnych kolumn indeksu. To da nam przesunięcie w rzędach.

Więc jeśli weźmiemy to i połączymy pierwszy indeks oparty na 0 z indeksem opartym na 1, dopasujemy 521 w 522 wierszach. Dokładnie tego byśmy się spodziewali, ponieważ druga tabela nie przyjmie indeksu zerowego, ponieważ zaczyna się od jedynki.

Następnie po prostu rozwiniemy to poprawnie, używając tylko ISO Weeknumber.

To daje nam numer z poprzedniego tygodnia. A teraz po prostu rozszerzymy nasze oryginalne grupowanie i usuniemy pole WeekEnding oraz oryginalny numer tygodnia ISO, aby uniknąć duplikatów.

Jeśli umieścimy to w tabeli, zobaczysz, że są to dokładnie takie same wyniki, jak przy użyciu techniki DAX. Działa dokładnie tak, jak nasza miara DAX, ale w tym przypadku nie musieliśmy używać przesunięć.

Wniosek

Są to dwa różne sposoby na bardzo wszechstronne znalezienie numeru z poprzedniego tygodnia. W pierwszej technice widać siłę przesunięć w wykonywaniu tego, co w przeciwnym razie byłoby trudną kalkulacją, dość prostą. Druga technika działa dokładnie tak samo, jak nasza miara DAX, ale w tym przypadku nie musieliśmy używać przesunięć.

Mamy nadzieję, że ten samouczek okaże się pomocny. Możesz obejrzeć pełny samouczek wideo poniżej i sprawdzić powiązane linki, aby uzyskać więcej podobnych treści.

Dzięki!