Tabela kursów walut — rozliczanie brakujących danych w języku DAX

Pokażę ci, jak radzić sobie z tabelą kursów walut, w której niektóre dni nie są uwzględniane . Te dni mogą być weekendami lub świętami.

Czasami może to nie stanowić problemu, zwłaszcza jeśli firma, dla której przygotowujesz raport, nie jest otwarta w weekendy i święta. Ale jeśli tak się stanie, możesz otrzymać nieprawidłowe sumy na koniec miesiąca.

Wiedza o tym, jak radzić sobie z brakującymi danymi, pozwoli ci rzetelnie porównać sprzedaż, jeśli używanych jest wiele walut. Mogą na przykład być dni, w których myślisz, że całkowita sprzedaż spada, ponieważ wartość waluty spadła. W rzeczywistości łączna sprzedaż może być niska, ponieważ nie uwzględniasz wszystkich dni w danym okresie.

W tym przykładzie wezmę ostatni dostępny kurs waluty i użyję go w brakujących datach, aby wypełnić miejsce. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Spis treści

• Podane dane w tabeli kursów walut
• Miara kursu waluty
• Bieżąca miara daty
• Ostatnia data waluty
• Ostatnia zgłoszona data waluty
• Ostatni podany kurs waluty
• Przeliczona sprzedaż przy użyciu ostatniego zgłoszonego kursu w porównaniu z bieżącą datą
• Czyszczenie danych
• Wniosek

Podane dane w tabeli kursów walut

Poniżej znajduje się tabela faktów, czyli tabela kursów walut . Wykorzystuje euro jako walutę bazową.

Pokazuje dane ze stycznia 2016 r. wraz z powiązanym kursem krzyżowym każdego dnia.

Wszystko wygląda dobrze od 1 stycznia. Ale kiedy trafię na 26-go, następna data w kolejnym rzędzie to 29-ty.

Oznacza to, że brakuje mi danych z 27 i 28 .

Pamiętaj, że niektórzy mogą preferować użycie dodatku Power Query do obsługi tej sytuacji. Na razie chcę się skupić na DAX.

Teraz powiedzmy, że chcę użyć 0,920 27 i 28. To jest kurs krzyżowy używany 26. Jak to zrobić za pomocą języka DAX?

Niektórzy mogą powiedzieć, że można to łatwo zrobić w Excelu. Mogą mieć do pewnego stopnia rację.

Ale przewaga usługi LuckyTemplates nad programem Excel polega na możliwości dalszego zagłębiania się w dane z różnych perspektyw bez konieczności ponownego wykonywania lub zmiany czegokolwiek, co najprawdopodobniej miałoby miejsce w programie Excel.

Pokażę Ci wszystkie środki, które musisz rozważyć, a także różne kroki, które podjąłbym, aby uzupełnić brakujące dane z tabel kursów walut.

Miara kursu waluty

Najpierw pokażę ci mój główny stół. Mam tu już trochę danych, w tym datę i kurs waluty dla euro .

W tym przypadku kurs waluty wykorzystuje po prostu funkcję .

Ponieważ mam do czynienia z jednym kursem walutowym dziennie, łatwo jest używać funkcji takich jak SUM , , lub cokolwiek podobnego. Dane wyświetlane w wierszu miałyby całkowity sens.

Jedynym problemem jest to, co stanie się z Total .

To pokazuje Sumę jako 26,693, co nie ma żadnego sensu. Dosłownie podsumowuje wszystkie kursy walut w ciągu dnia. Z tego powodu musiałbym znaleźć sposób na iterację w ciągu dni, aby uzyskać prawdziwy Total .

Przyjrzę się więc mojej miarie daty waluty , aby spróbować rozwiązać ten problem.

Bieżąca miara daty

Osobiście zawsze wykonuję miarę Bieżąca data , która wprowadza datę, w której się znajduję, w kontekście Bieżącego filtra .

Wolę też używać . W tym przykładzie użycie funkcji MAX da mi sumę 1/31, co miałoby większy sens, ponieważ jest to ostatnia podana data.

Ostatnia data waluty

Moja tabela zawiera również datę ostatniej waluty . Otrzymuję to za pomocą funkcji i odwołując się do tabel FactCurrencyRates i Date .

Chociaż pokazuje to funkcję LASTDATE , możliwe jest również użycie MAX . Wykorzystuje to samo założenie, co tabela Bieżąca data , ale podaje określony punkt w czasie w stosunku do tabeli Kursy walut .

Przewijając w dół, widzę, że nie ma danych dla 27 i 28 dnia miesiąca.

Aby zająć się tymi brakującymi dniami, omówię kolumnę Data ostatniej zgłoszonej waluty .

Ostatnia zgłoszona data waluty

Data ostatniej zgłoszonej waluty określi, jaki kurs zostanie zastosowany w przypadku brakujących dni. Koncepcja polega na tym, że ostatni zgłoszony kurs waluty będzie taki sam, jak w dniach z brakującymi danymi .

Ponieważ nie ma danych zarówno z 27, jak i 28, oznacza to, że zastosowane zostaną dane z 26.

Nasz przykład pokazuje również, że data ostatniej zgłoszonej waluty niekoniecznie musi przypadać na dzień poprzedni. Ponieważ nie ma również danych dla 27-go, nadal musiałbym wrócić do 26-go, aby dowiedzieć się, jaki kurs zastosować 28-go.

Zanim przejdę do miary pokazującej, jak to zostało zrobione, pamiętaj, że wszystko tutaj dzieje się w kontekście działania filtra. Muszę się zastanowić, na jakich konkretnych danych chcę się skupić.

Ponieważ jestem w rzędzie 27 dnia miesiąca, chcę usunąć wszelkie odniesienia do filtra i zamiast tego skupić się na 26 dniu. Oznacza to przyniesienie lub , ponieważ pozwalają mi one modyfikować zastosowany filtr. Nie należy tego mylić z funkcją , która może tylko dodatkowo ograniczać dane.

Oto miara, której użyłem dla daty ostatniej zgłoszonej waluty . W tym przykładzie użyłem CALCULATE .

Zawsze staram się znaleźć jak najwięcej zmiennych. Muszę tylko spojrzeć na zakres każdej zmiennej, ponieważ zmienne przyjmują wartość miejsca, w którym są zdefiniowane .

W tym przypadku użyję Bieżącej daty , ponieważ wiem, że ta zmienna nie zmieni się niezależnie od tego, jakich funkcji użyję.

Zauważ, że znalezienie tej zmiennej nie jest wymagane. To tylko nawyk, który pozwala mi sprawdzić zakres każdej zmiennej, której będę używał w pomiarze.

Wracając do miary, jak powiedzieć DAXowi, aby usunął filtry dla bieżącej daty i podał mi datę ostatniej zarejestrowanej waluty ?

Najpierw użyję funkcji FILTER dla tabeli (DimDate).

Funkcja ALL usuwa zastosowane filtry, otwierając dla mnie tabelę DimDate.

Następnie używam MIN , aby powiedzieć DAX, że chcę użyć części kolumny DimDate, która jest równa Dacie ostatniej waluty lub Dacie bieżącej .

Co zrobić, jeśli data ostatniej waluty i data bieżąca nie są takie same? Następnie zawsze używaj daty ostatniej waluty .

Dlatego między 1/26 a 1/27 miara zajmie 1/26. Od tego momentu właściwa data zostanie zastosowana do tabeli kursów walut , aby uzyskać ostatni zgłoszony kurs waluty .

Ostatni podany kurs waluty

Pozwól, że przeciągnę ostatni zgłoszony kurs waluty do mojej tabeli.

Zobaczysz, że 27-go i 28-go używa się teraz 0,9200 jako kursu waluty , ponieważ jest to ten sam kurs, co 26-go.

Poniżej znajduje się miara, której użyłem do określenia ostatniego zgłoszonego kursu waluty .

Ponownie, zawsze zaczynam od zdefiniowania pewnych zmiennych. W tym przypadku używam wybranej waluty i daty ostatniej zgłoszonej waluty .

Zamierzam użyć Wybranej Waluty, ponieważ mam tu do czynienia z różnymi walutami. Dlatego muszę dokładnie określić, jaka konkretna waluta jest oceniana w danym momencie.

W tym przykładzie dostęp do różnych walut można uzyskać za pomocą podanego fragmentatora.

Oczywiście nie musi to być krajalnica, która jest w użyciu. Może też pochodzić ze stołu lub wizualizacji. Liczy się to, że istnieje filtr umożliwiający przeglądanie danych w różnych walutach.

Jeśli chodzi o LastReportedDate , jest to to samo, co data ostatniej zgłoszonej waluty , o której mówiłem wcześniej.

W przypadku zmiennej Rate użyłem funkcji .

Wprowadzam Crossrate z tabeli faktów dla CurrencyRates .

Ponieważ mam wiele walut w jednej tabeli, nie mogę po prostu użyć daty jako pojedynczego punktu odniesienia. Musi to być kombinacja odpowiedniej waluty i właściwej daty.

Właśnie dlatego odwołuję się również do paska walutowego równego Currency Selected .

Odwołuję się również do LastReportedDate .

Dlatego patrząc na tabelę, trzy razy pokazuje 26., zanim przeskoczy na 29.

Przeliczona sprzedaż przy użyciu ostatniego zgłoszonego kursu w porównaniu z bieżącą datą

Jestem teraz na ostatniej prostej, która polega na przeliczeniu sprzedaży przy użyciu ostatniego zgłoszonego kursu waluty .

Zasadniczo miara bierze po prostu ostatni zgłoszony kurs waluty i mnoży go przez miarę podstawową , która w tym przypadku jest sprzedażą całkowitą .

Po zastosowaniu tej miary zobaczysz, że te dwie daty są puste. Oznacza to, że w tych terminach nie ma sprzedaży .

Okazuje się też, że są wyprzedaże 27 i 28 (gdzie wcześniej nie było danych walutowych).

Ponieważ odniosłem się do ostatniego zgłoszonego kursu waluty , liczby te zostały przeliczone na euro.

Tym razem pokażę Ci przekonwertowaną sprzedaż przy użyciu bieżącej daty.

Tym razem patrzy tylko na bieżącą datę , zamiast brać pod uwagę datę ostatniego zgłoszenia .

Kiedy umieściłem tę miarę w tabeli w innej kolumnie, pokazuje puste miejsca tylko w 27 i 28.

Porównując przekonwertowaną sprzedaż przy użyciu Kursu z ostatniego raportu i bieżącej daty , Suma pokazuje ogromną różnicę.

Tam, gdzie przekonwertowana sprzedaż przy użyciu daty ostatniego zgłoszenia pokazuje łącznie ponad 4 miliony, przekonwertowana sprzedaż przy użyciu bieżącej daty pokazuje łącznie tylko 3,8 miliona.

Dlatego bardzo ważne jest, aby zrozumieć, jakiej zmiennej używasz. Jeśli spojrzysz na sumę, wygląda na to, że euro odniosło duży sukces, obniżając wartość. Ale w rzeczywistości spadek sumy wynika z nieuwzględnionych danych w tabeli kursów walut.

Czyszczenie danych

Teraz, gdy uzupełniłem brakujące dane, zamierzam wyczyścić tabelę.

Właśnie dodałem tutaj kolumnę Sformatowana sprzedaż , która wyraźnie pokazuje liczby w euro.

Ponieważ przeszedłem przez tak wiele kroków, zgromadziłem tutaj całkiem sporo kolumn.

Ale nie wszystkie z nich są potrzebne do prezentacji danych. Więc pójdę dalej i pozbędę się ich, usuwając je w okienku Wartości .

Po ich usunięciu otrzymuję czystszą tabelę z pokazanymi tylko niezbędnymi danymi.

Pamiętaj, że nadal mogę zmienić walutę za pomocą fragmentatora po prawej stronie.

Aby moja tabela była bardziej dokładna, dodam wszystkie inne waluty, wyłączając opcję Single Select .

Spowoduje to wyświetlenie moich liczb w USD, euro i funtach brytyjskich.

Widzę tu również puste miejsca reprezentujące dni, w których nie ma sprzedaży.

Usunę je, wyłączając opcję „ Pokaż elementy bez danych ”.

Teraz tabela zawiera tylko wiersze reprezentujące daty ze sprzedażą.

Daje mi to dobre porównanie liczb opartych na różnych walutach.

Sumy pokazują również prawidłowe liczby, ponieważ użyłem do iteracji w każdym z wierszy.

To jest bardzo ważne. Jeśli nie użyjesz SUMX , skończy się to na dodaniu wszystkich kolumn bez zastanawiania się, czy używany jest właściwy kurs waluty.

Tworzenie tabeli konwersji walut w usłudze LuckyTemplates
Wielowalutowa logika w usłudze LuckyTemplates — Przykład WYSZUKIWANEJ
WARTOŚCI Formatuj waluty w usłudze LuckyTemplates przy użyciu języka DAX

Wniosek

Jak wspomniałem wcześniej, niekoniecznie jest to jedyny sposób radzenia sobie z brakującymi danymi. W rzeczywistości istnieje mnóstwo innych sposobów, aby to zrobić.

Na przykład w niektórych przypadkach możesz użyć danych z początku miesiąca i zastosować tę samą liczbę w całej tabeli kursów walut. Będę omawiać te inne podejścia w przyszłości w osobnych blogach.

Na razie zastosowałem to podejście, ponieważ użycie ostatniej zgłoszonej stawki ma dla mnie największy sens. Uważam, że jest to najbardziej intuicyjna opcja.

Jedną rzeczą, którą chciałbym również podkreślić, jest potrzeba zrozumienia wszystkiego z kontekstu filtra. Jak nadpisać filtr? Jak wprowadzić ostatnio zgłoszone dane? Pomogą ci one zrozumieć, jak w końcu uzyskać właściwe liczby.

Wszystkiego najlepszego,