Schritt-für-Schritt-Anleitung 
Ein Leser möchte wissen, wie er die Anzahl der aktiven Nutzer per 31.12. eines Jahres mit Power BI ermitteln kann. Ihm liegen das Eintritts- und das Austrittsdatum der Nutzer vor. In diesem Artikel beantworte ich seine Frage nachvollziehbar und Schritt für Schritt.
Die Frage meines Lesers
Hier zunächst einmal die vollständige Frage, die mein Leser mir über mein Forum gestellt hat:
Hallo Herr Trummel,
vorab schon mal ein dickes Danke für die ganzen beantworteten Fragen. Hier gab es schon den einen oder anderen super Tipp.
Aktuell kommen wir bei einer Anforderung nicht weiter.
Wir haben eine ganz einfache Tabelle mit Nutzernummer, Eintrittsdatum und Austrittsdatum.
Wir haben einen Datenschnitt mit Jahreskacheln und wollen nun die Anzahl der aktiven Nutzer per 31.12. eines Jahres sehen.
Ich werde nun einen Bericht erzeugen, in welchem wir über einen Datenschnitt ein bestimmtes Jahr auswählen können. Eine Tabellenvisualisierung wird dann nur die Nutzer zeigen, welche per 31.12. des selektierten Jahres aktiv im Unternehmen waren.
Den fertigen Bericht können Sie vorab schon einmal hier testen. Weiter unten erhalten Sie dann auch die Möglichkeit, die Power BI Desktop-Datei kostenfrei herunterzuladen.
Die Tabelle Nutzer
Gehen wir einmal von folgenden Daten in der Tabelle Nutzer aus:
Beachten Sie, dass es einerseits Personen gibt, die Stand heute (17.3.2024) bereits ausgetreten sind (Nutzer 2) sowie andere Nutzer, die erst in der Zukunft eintreten werden (Nutzer 4). Es gibt Personen, deren Austrittsdatum bereits feststeht (Nutzer 1) und es gibt Personen, deren Austrittsdatum noch nicht feststeht (Nutzer 3).
Das Datenmodell
In unserem Datenmodell existiert neben der Tabelle Nutzer noch eine Datumstabelle namens Kalender. Beachten Sie, dass die Tabellen nicht miteinander verbunden sind. Dies würde bei der hier gezeigten Lösung zu falschen Ergebnissen führen!
Der Bericht
Datenschnitt zur Auswahl der Jahreskacheln
Wir beginnen damit, einen Datenschnitt im Bericht zu platzieren, über den ein Jahr aus dem Kalender auswählen können. Ich habe die Visualisierung so eingestellt, dass sie Kacheln zeigt und nur eine Einfachauswahl zulässt.
Measure mit dem Vergleichsdatum per 31.12.
Nun erzeugen wir ein Measure, welches für das im Datenschnitt gewählte Jahr ein Vergleichsdatum erzeugt. Dieses soll immer der 31. Dezember des gewählten Jahres sein.
Vergleichsdatum =
VAR vJahr = SELECTEDVALUE(Kalender[Jahr])
RETURN
DATE(vJahr, 12, 31)Die Funktion SELECTEDVALUE liest uns das im Datenschnitt gewählte Jahr aus. Die Funktion DATE erzeugt dann für dieses Jahr das Datum zum 31. Dezember.
Warum erzeuge ich hierfür ein eigenes Measure? Dies hat den Vorteil, dass ich das Vergleichsdatum jetzt auch im Bericht ausgeben kann (s. weiter unten).
Measure mit Anzahl der Nutzer
Nun kommt der schwierigste Teil. Wir möchten in der Tabelle Nutzer nur diejenigen Zeilen zählen, bei denen das Vergleichsdatum zwischen dem Eintritts- und Austrittsdatum liegt. Zu beachten ist, dass das Austrittsdatum natürlich auch leer sein kann. Wir müssen also die gesamte Tabelle Nutzer Zeile für Zeile durchlaufen und für jede Person prüfen:
- ob das Vergleichsdatum größer oder gleich dem Eintrittsdatum ist und
- ob das Vergleichsdatum kleiner oder gleich dem Austrittsdatum ist oder
- ob das Austrittsdatum leer ist.
Diese Aufgabe kann die Funktion FILTER übernehmen. Im 1. Argument der Funktion geben wir die zu prüfende Tabelle ein, im 2. Argument die Filterbedingung. Um die FILTER-Funktion herum bauen wir die Funktion COUNTROWS, welche alle Zeilen der gefilterten Tabelle zählt.
Anzahl Nutzer =
COUNTROWS(
FILTER(
Nutzer,
[Vergleichsdatum] >= Nutzer[Eintrittsdatum] &&
(
ISBLANK(Nutzer[Austrittsdatum]) ||
[Vergleichsdatum] <= Nutzer[Austrittsdatum]
)
)
)Die Funktion ISBLANK prüft, ob die Spalte Austrittsdatum leer ist. Der Operator && erzeugt eine UND-Verknüpfung, der Operator || eine ODER-Verknüpfung.
Aktive Nutzer im Bericht ausgeben
Um die Nutzer auch sehen zu können, verwenden wir eine Tabellenvisualisierung, in welche wir die Felder Nutzernummer, Eintrittsdatum und Austrittsdatum legen. Wichtig ist hier, dass wir auch das Measure Anzahl Nutzer in der Tabelle platzieren. Die Visualisierung zeigt dann nur die Zeilen an, für welche das Measure ein Ergebnis ermittelt. Das bedeutet im Umkehrschluss, dass wir alle Nutzer, die zum gewählten Zeitpunkt nicht aktiv im Unternehmen sind, ausblenden. Wählen wir nun das Jahr 2023 im Datenschnitt, dann sehen wir lediglich die Nutzer 1 und 3.
Überschrift für die Tabellenvisualisierung
Damit auch wirklich klar ist, welche Auswertung unsere Tabelle zeigt, erzeuge ich noch ein Measure mit einer passenden Überschrift:
Überschrift = "Nutzer am " & FORMAT([Vergleichsdatum], "dd.mm.yyyy", "de-DE")Mit der Funktion FORMAT stelle ich sicher, dass wir das Datum immer im europäischen Format sehen. Dies kann wichtig sein, wenn der Browser zum Beispiel auf Englisch eingestellt ist. In einem solchen Fall würden wir das Datum sonst in der amerikanischen Syntax erhalten. Dies kann ich mit der FORMAT-Funktion aber vermeiden.
Sie können nun die fertige Power BI Desktop-Datei kostenfrei herunterladen:
Fazit
Die Aufgabe vor der mein Leser steht, klingt erst einmal sehr aufwendig. Wenn wir jedoch wissen, wie wir ein Vergleichsdatum erzeugen und mit der DAX-Funktion FILTER die Nutzertabelle entsprechend filtern können, dann haben wir schon fast die Lösung. Mit der Funktion COUNTROWS müssen wir dann nur noch die Zeilen der Tabelle zählen.