Różnica między czasami w Power BI DAX
DAX
Power BI
Analityka danych
W ciągu zaledwie jednego tygodnia dostaliśmy od Was 2 niemal identycznie brzmiące pytania, w ramach opieki mentorskiej po szkoleniu e-learningowym Power BI (tak, do szkoleń e-learningowych dostajesz opiekę mentorską!). Poniżej zamieściliśmy artykuł, w którym dokładnie opiszemy jak poradzić sobie z tym problemem. Tych którzy cenią sobie bardziej vlogi zapraszam na lewo, oraz na nasz kanał YouTube.
Pytanie wydaje się dość banalne – w jaki sposób policzyć różnice między dwoma timestampami (data + czas) i pokazać ją w postaci dd:hh:mm:ss, gdzie d – liczba dni, h – liczba godzin, m – liczba minut, s – liczba sekund.
Rozwiązanie niestety już takie banalne nie jest. Istnieje co prawda bardzo fajna funkcja w DAX DATEDIFF, która pozwala policzyć np. liczbę minut czy też sekund między dwoma czasami (datami), jednak ma ona w naszym wydaniu zasadniczą wadę. Przyjrzyjmy się działaniu tejże funkcji na takim zestawie:
Funkcja zwraca liczbę wszystkich sekund pomiędzy dwoma datami. Nie jest możliwe wyciągnięcie samych sekund nie jako nadmiarowych nad minutami, analogicznie nie można wyciągnąć minut powyżej pełnych dni i tak dalej…
Co zatem możemy w takiej sytuacji zrobić? Musimy się niestety troszeczkę pobawić naszymi danymi i kodem. Jako pocieszenie pamiętajcie, że takie zadanie musimy rozklikać raz a potem zapisać sobie do dalszego wykorzystania jak wzorzec DAX.
Skoro możemy łatwo policzyć ile sekund mija między dwiema datami, a liczba sekund w minucie (60), godzinie (3600) , dniu (86400) jest stała to możemy zapisać formułę, która policzy odpowiednie elementy a na koniec zadbamy o wyświetlenie tego w odpowiednim formacie.
Przyjrzyjmy się formule, jej wynikowi a następnie przeanalizujmy poszczególne jej elementy:
diff =
VAR __sekundy =
DATEDIFF ( timestamps[Data start], timestamps[Data koniec], SECOND )
VAR __dni =
INT ( DIVIDE ( __sekundy, 86400 ) )
VAR __godziny =
INT ( DIVIDE ( __sekundy – __dni * 86400, 3600 ) )
VAR __minuty =
INT ( DIVIDE ( __sekundy – __dni * 86400 – __godziny * 3600, 60 ) )
VAR __sekundy_out =
ROUND ( __sekundy – __dni * 86400 – __godziny * 3600 – __minuty * 60, 0 )
VAR __out =
„d:” & FORMAT ( __dni, „00” ) & ” h:”
& FORMAT ( __godziny, „00” ) & ” m:”
& FORMAT ( __minuty, „00” ) & ” s:”
& FORMAT ( __sekundy_out, „00” )
RETURN
__out
Zwróć proszę uwagę, że powyższa formuła jest wprowadzona do kolumny obliczeniowej (pracujemy w kontekście wiersza). Różnica między kontekstami a także magiczny i tajemniczy mechanizm „przejścia kontekstu” jest jedną z najważniejszych osobliwości DAX. O tych osobliwościach opowiadamy na szkoleniu:
Poznaj nasze szkolenia Power BI, które mamy w swojej ofercie:
Co się dzieje w naszej formule?
var __sekundy = DATEDIFF(timestamps[Data start], timestamps[Data koniec], second )
Tutaj obliczamy liczbę sekund pomiędzy dwiema datami i wykorzystujemy do tego funkcję DATEDIFF.
var __dni = int ( DIVIDE ( __sekundy, 86400 ) )
Dzieląc liczbę sekund przez 86400 dostaniemy liczbę dni. Funkcja INT ma tutaj za zadanie obciąć część dziesiętną wyniku. Tym samym dostaniemy liczbę pełnych dni.
var __godziny = int(
DIVIDE( __sekundy – __dni * 86400, 3600 ) )
Podobnie jak powyżej interesuje nas obcięcie części dziesiętnej. Tym razem od obliczonych sekund odejmujemy już sekundy, które wystąpiły w policzonych uprzednio pełnych dniach. Dostaniemy w ten sposób informacje o sekundach „nadmiarowych” względem dnia. Wyznaczamy liczbę godzin dzieląc powstały wynik przez 3600.
var __minuty =
int (
DIVIDE( __sekundy – __dni * 86400 – __godziny * 3600, 60 ))
var __sekundy_out = round ( __sekundy – __dni * 86400 – __godziny * 3600 – __minuty * 60, 0
W powyższych krokach logika jest dokładnie taka sama jak poprzednio.
Pozostaje nam już tylko „posklejać” to w jedną całość:
VAR __out =
„d:” & FORMAT ( __dni, „00” ) & ” h:”
& FORMAT ( __godziny, „00” ) & ” m:”
& FORMAT ( __minuty, „00” ) & ” s:”
& FORMAT ( __sekundy_out, „00” )
Trickiem tutaj jest wykorzystanie funkcji FORMAT z kodem formatu „00”, który zaopiekuje się wyświetlaniem poszczególnych części zawsze dwucyfrowo.
Pamiętaj, że ta funkcja jest zbudowana w kolumnie obliczeniowej w DAX. Takiej kolumny nie jesteś w stanie zsumować na raporcie (jest tekstem). Jeśli chciałbyś skorzystać z analogicznego obliczenia na poziomie miary – musisz w jakiś sposób przekazać informacje o czasach do miary. Jednym z rozwiązań może być utworzenie np. takiej miary:
