Przegląd
Jest to trudna sprawa, o którą prosi PowerBI, więc uporządkowane podejście może być trudne do znalezienia.
Największym problemem jest to, że model danych PowerBI nie obsługuje koncepcji liczenia działającego - przynajmniej nie tak, jak robimy to w Excelu. W programie Excel kolumna może odwoływać się do wartości występujących w „poprzednim wierszu” tej samej kolumny, a następnie być dostosowywana przez „dzienną zmianę” wymienioną w innej kolumnie.
PowerBI może to naśladować, sumując wszystkie codzienne zmiany w niektórych podzbiorach wierszy. Bierzemy wartość daty w naszym bieżącym wierszu i tworzymy filtrowaną tabelę, w której wszystkie daty są mniejsze niż data bieżącego wiersza, a następnie podsumowujemy wszystkie codzienne zmiany z tego podzbioru. Może się to wydawać subtelną różnicą, ale jest dość znacząca:
Oznacza to, że nie ma możliwości „zastąpienia” naszej sumy bieżącej. Jedyną matematyką, która jest wykonywana, jest kolumna zawierająca codzienne zmiany - kolumna zawierająca „sumę bieżącą” jest tylko wynikiem - nigdy nie jest używana w obliczeniach kolejnych wierszy.
Musimy porzucić koncepcję „resetu” i zamiast tego wyobrazić sobie utworzenie kolumny zawierającej wartość „korekty”. Nasza korekta będzie wartością, którą można uwzględnić, aby po spełnieniu opisanych warunków suma dziennych sald i korekt wyniosła 1.
Jeśli spojrzymy na obliczone wyniki podane przez OP, zobaczymy, że wartość naszej sumy bieżącej w dniu „niepracującym” tuż przed dniem „roboczym” daje nam potrzebną kwotę, która, jeśli zostanie odwrócona, wyniesie zero i spowodować, że suma bieżąca w każdym kolejnym dniu roboczym wzrośnie o jeden. To jest nasze pożądane zachowanie (z jednym problemem, który zostanie opisany później).
Wynik
Most Recent Date Prior to Work =
CALCULATE(
Max(Leave[Date]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] = EARLIER(Leave[Date]) -1 && Leave[Type] <> "Working" && Earlier(Leave[Type]) = "Working"
))
Pomaga poznać różnicę między kontekstami wierszy i filtrów oraz sposób działania EARLIER w celu wykonania tego obliczenia. W tym scenariuszu możesz myśleć o „WCZEŚNIEJ” jako oznaczającym, że „to odniesienie wskazuje na wartość w bieżącym wierszu”, a w innym przypadku odniesienie wskazuje na całą tabelę zwróconą przez „ALLEXCEPT (Leave, Leave [Id]).” W tym w ten sposób znajdujemy miejsca, w których bieżący wiersz ma typ „Pracujący”, a wiersz z poprzedniego dnia ma inny typ.
Most Recent Date Prior to Work Complete =
CALCULATE(
Max(Leave[Most Recent Date Prior to Work]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] <= EARLIER(Leave[Date])
))
Obliczenia imitują operację typu „wypełnij”. Mówi: „Patrząc na wszystkie wiersze, których data jest wcześniejsza niż data W TYM wierszu, zwróć największą wartość w polu„ Ostatnia data przed pracą ”.
Daily Balance Adjustment =
CALCULATE(
SUM(Leave[Running Daily Balance]),
FILTER(
ALLEXCEPT(Leave, Leave[Id]),
Leave[Date] = EARLIER(Leave[Most Recent Date Prior to Work Complete])
))
Teraz, gdy w każdym wierszu znajduje się pole wyjaśniające, gdzie znaleźć saldo dzienne, które można wykorzystać jako naszą korektę, możemy po prostu spojrzeć w górę ze stołu.
Adjusted Daily Balance = Leave[Running Daily Balance] - Leave[Daily Balance Adjustment]
I na koniec dostosowujemy naszą sumę bieżącą dla ostatecznego wyniku.
Problem
Podejście to nie rozwiązuje problemu zliczania, o ile bieżące saldo dzienne nie spadnie poniżej zera. Wcześniej udowodniono, że się mylę, ale powiedziałbym, że nie można tego osiągnąć w samym języku DAX, ponieważ powoduje to zależność cykliczną. Zasadniczo stawiasz wymaganie: użyj wartości zagregowanej, aby określić, co powinno zostać uwzględnione w agregacji.
To tyle, ile mogę ci przynieść. Mam nadzieję, że to pomoże.
