Ostatnio znalazłem się w sytuacji, gdy w Power BI musiałem policzyć różnicę między dwoma datami, nie uzwględniając weekendów. Wymaganie wydawało mi się łatwe, do czasu gdy uświadomiłem sobie, że w zasadzie w Power BI nie istnieją funkcje, które pozwolą mi je zrealizować lekko i przyjemnie.

Przeglądając wyniki na forum community znalazłem różne rozwiązania, np.: https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662 czy https://community.powerbi.com/t5/Desktop/calculate-number-of-working-days-between-2-dates/td-p/55456 i jeszcze więcej googlając, jednak w zasadzie każde służyło wyłączni temu, aby zwrócić liczbę dni pracujących pomiędzy zadanymi datami.

Ja dla odmiany potrzebowałem bardzo precyzyjnych informacji, np. jeśli datą początku była 2018-10-25 14:00 zaś końca 2018-10-25 15:00 wówczas oczekiwałem jako wyniku „0”, nie „1”. Podobnie w sytuacji, gdy datą początku była 2018-10-25 14:00, zaś końca 2018-10-26 13:59 – to wciąż powinno być „0”. Taką właśnie informację zwraca funkcja „DATEDIFF(Start, End, DAY)” w Power BI. Ale DATEDIFF niestety także wlicza w swój wynik weekendy, także w sytuacji, gdy datą początku byłaby 2018-10-26 14:00, a końca 2018-10-29 15:00 funkcja zwróciłaby 3, podczas gdy ja oczekuję 1.

Podczas moich poszukiwań natrafiłem także na ten sprytny post: https://powerapps.microsoft.com/en-us/blog/excluding-weekends-and-holidays-in-date-differences-in-powerapps/, który pozwoliłby mi precyzyjnie policzyć liczbę dni bez weekendów, jednak nie mógłbym go użyć do policzenia liczby godzin czy minut.

Rozwiązanie

Wszystkie znalezione przeze mnie rozwiązania skupiały się na liczeniu liczby dni pracujących. Uświadomiłem sobie, że w moim wypadku to, co istotne, to liczba dni wolnych w zadanym przedziale czasu.

Tym samym stworzyłem tabelę zawierającą listę dat od 1 stycznia 2017 to 30 grudnia 2027. Każdy dzień przypadający w weekend był wyróżniony.

Tabelę stworzyłem używając pliku Excel jako źródła danych. W pliku Excel kolumna, odpowiadająca za ocenę, czy danego dnia jest weekend, czy nie, zawiera formułę:

=IF(WEEKDAY(A2,2)>5,FALSE,TRUE)

Następnie użyłem funkcji „DATEDIFF” i od jej wyniku odjąłem odpowiednio liczbę dni, godzin lub minut zależnie od liczby dni wolnych znajdujących się w przedziale.

Formuła, jakiej użyłem wygląda następująco. Dla dni:

DATEDIFF([StartDateTime],[EndDateTime], DAY) -
(
   CALCULATE (
        COUNTROWS('Weekends'),
        'Weekends'[IsWeekend] = TRUE( ),
        DATESBETWEEN('Weekends'[Date], [StartDateTime],[EndDateTime])
   )
)

Dla godzin:

DATEDIFF([StartDateTime],[EndDateTime], HOUR) -
(
   CALCULATE (
        COUNTROWS('Weekends'),
        'Weekends'[IsWeekend] = TRUE( ),
        DATESBETWEEN('Weekends'[Date], [StartDateTime],[EndDateTime])
   )*24  --24 hours per day
)

I dla minut:

DATEDIFF([StartDateTime],[EndDateTime], MINUTE) -
(
   CALCULATE (
        COUNTROWS('Weekends'),
        'Weekends'[IsWeekend] = TRUE( ),
        DATESBETWEEN('Weekends'[Date], [StartDateTime],[EndDateTime])
   )*1440  --24 hours per day * 60 minutes per hour
)

Używając tego podejścia zrealizowałem wymaganie i rozwiązanie działa jak powinno. Osiągnąłem dzięki niemu precyzję w liczeniu różnicy między dwoma datami, korzystając z funkcji „DATEDIFF” i wynik jest nie zaburzony przez dni wolne.