Vor rund einem Jahr lernte ich Power BI kennen, und es war Liebe auf den ersten Klick. Begeistert visualisierte ich Daten aus Microsoft Dynamics CRM für meine Kolleginnen. Doch dann stellte sich eine Herausforderung zwischen uns: die Zeit. Dynamics CRM speichert Daten in UTC, während ich die Werte in mitteleuropäischer Zeit (CET/CEST) benötige. Unsere Beziehung wurde auf die Probe gestellt – bis ChatGPT mit einer DAX-Formel zur Versöhnung beitrug.
Das erste Treffen
Power BI wurde mir von einem externen Partner empfohlen, da ich mit den Dashboards von Dynamics CRM nicht zufrieden war. Die nötigen Lizenzen waren vorhanden, also stand einem ersten Date nichts im Wege. Unterstützt von einem erfahrenen Datenanalysten, wurden mit Power BI Desktop die gewünschten Tabellen aus dem CRM importiert und die ersten Berichte erstellt. Die regelmässige Aktualisierung der Daten und den Zugang für meine Kolleginnen definierten wir auf PowerBI.com.
Der Honeymoon und die erste Krise
Die ersten Wochen verliefen wunderbar. Power BI eröffnete mir immer tiefere Einblicke in die Daten aus Dynamics CRM. Auch meine Kolleginnen interessierten sich zunehmend stärker für die Früchte unserer Beziehung – bis es zu Unstimmigkeiten kam.
Gefordert war eine Übersicht der Anzahl neuer Mitglieder pro Jahr. Ich erstellte eine Matrix, welche die Mitglieder basierend auf ihrem Eintrittsdatum summierte. Doch die Resultate stimmten nicht mit den Zahlen überein, die ich direkt im CRM ermittelt hatte.
Der Weg durch die Verzweiflung
Die Ursache war schnell gefunden. Wenn ich als Benutzerin in der Zeitzone CET/CEST im CRM das Datum „01.01.2024“ erfasse, entspricht dies im Format „Datum/Zeit“ dem Wert „01.01.2024 00:00:00“. In der Datenbank wird jedoch dieses Datum im Format UTC (koordinierte Weltzeit) gespeichert – was hier „31.12.2023 23:00:00“ entspricht.
Wie kommen wir hier zu einem Konsens? Als erstes suchte ich in Power BI eine Einstellung für die Zeitzonen – ohne Erfolg. Dann konsultierte ich intensiv Freund Google, welcher mich in Richtung Power Query Editor leitete. Doch in dieser Funktion von Power BI fand ich mich nicht zurecht. Ich wandte mich schliesslich an unseren externen Partner und eröffnete ein Ticket, um einen replizierbaren Lösungsansatz für die Umwandlung solcher Datumsfelder zu erhalten. Mittlerweile hatten nämlich auch andere Abteilungen ähnliche Schwierigkeiten mit Power BI und Dynamics CRM.
Die Versöhnung dank DAX
Monate später – das Ticket beim Partner noch offen, mittlerweile zu Microsoft eskaliert – startete ich nochmals einen Versuch. Ich fragte meinen neuen Freund ChatGPT um Rat.
ChatGPT schlug vor, in meiner Tabelle in Power BI Datenansicht eine neue Spalte mit folgender DAX-Formel zu erstellen:
Datum_GMT_Plus_1_Mit_DST =
VAR Jahr = YEAR(<DeinDatumsfeld>)
VAR Sommerzeit_Beginn = DATE(Jahr, 3, 31) – WEEKDAY(DATE(Jahr, 3, 31), 2) // Letzter Sonntag im März
VAR Sommerzeit_Ende = DATE(Jahr, 10, 31) – WEEKDAY(DATE(Jahr, 10, 31), 2) // Letzter Sonntag im Oktober
RETURN
IF(
<DeinDatumsfeld> >= Sommerzeit_Beginn && <DeinDatumsfeld> < Sommerzeit_Ende,
<DeinDatumsfeld> + TIME(2, 0, 0), // Sommerzeit (GMT+2)
<DeinDatumsfeld> + TIME(1, 0, 0) // Winterzeit (GMT+1)
)
Erklärung:
- Die Formel berechnet zuerst das Jahr des Datumsfeldes.
- Dann wird der letzte Sonntag im März (Beginn der Sommerzeit) und der letzte Sonntag im Oktober (Ende der Sommerzeit) für das angegebene Jahr berechnet.
- Anschliessend überprüft die Formel, ob das Datum in die Sommerzeit fällt:
– Sommerzeit: Es wird 2 Stunden zur UTC-Zeit addiert (GMT+2 / CEST).
– Winterzeit: Es wird nur 1 Stunde zur UTC-Zeit addiert (GMT+1 / CET).
Dank der neuen Spalte „Eintritt CET/CEST“ zeigt die entsprechende Matrix nun die korrekte Anzahl Eintritte im Jahr 2024.
Temporäres Happy End
Die Datumskonvertierung mit DAX bietet eine praktikable Lösung, wenn es sich um einzelne Datumsfelder handelt. In Dynamics CRM gibt es jedoch zahlreiche Datumsfelder, was die manuelle Anpassung jeder Spalte äusserst zeitintensiv und ineffizient macht.
Ich werde weitersuchen, um eine zentrale oder automatisierte Lösung für diese Herausforderung zu finden. Und wer weiss, vielleicht zeigt mir das CAS Business Intelligence & Analytics einen möglichen Weg für eine weiterhin glückliche und nachhaltige Beziehung mit Power BI ;-).
